Macro 84: Color Chart Data Points to Match Source Cell Colors
In the previous macro, we force each chart series to apply the same colors as their respective source
data ranges. This macro works the same way, but with data points. You would use this macro if you
wanted to force a pie chart to adopt the color of each data point’s source range.
How it works
In this case, we are setting the color to the color of the source range. We identify the source range for
each series by evaluating its series formula. The series formula contains the range address of the
source data. Passing that address to a range object, we can capture the exact color of cells, and then
use that to color the series.
Sub Macro84()
‘Step 1: Declare your variables
Dim oChart As Chart
Dim MySeries As Series
Dim i As Integer
Dim dValues As Variant
Dim FormulaSplit As String
‘Step 2: Point to the active chart
On Error Resume Next
Set oChart = ActiveChart
‘Step 3: Exit no chart has been selected
If oChart Is Nothing Then
MsgBox “You must select a chart first.”
Exit Sub
End If
‘Step 4: Loop through the chart series
For Each MySeries In oChart.SeriesCollection
‘Step 5: Get Source Data Range for the target series
FormulaSplit = Split(MySeries.Formula, “,”)(2)
‘Step 6: Capture Series Values
dValues = MySeries.Values
‘Step 7: Loop through series values and set color
For i = 1 To UBound(dValues)
MySeries.Points(i).Interior.Color = _
Range(FormulaSplit).Cells(i).Interior.Color
Next i
‘Step 8: Move to the next series
Next MySeries
End Sub
1. Step 1 declares five variables. We use oChart as the memory container for our chart,
MySeries as a memory container for each series in our chart, dValues in conjunction with
i to loop through the values in the series, and FormulaSplit to capture and store the
source data range.
2. This macro is designed so that we infer the target chart based on the chart selection. A chart
must be selected for this macro to run. The assumption is that we want to perform the macro
action on the chart we clicked on.
In Step 2, we set the oChart variable to the ActiveChart. If a chart is not selected, an error is
thrown. This is why we use the On Error Resume Next statement. This tells Excel to
continue with the macro if there is an error.
3. In Step 3, we check to see whether the oChart variable is filled with a chart object. If the
oChart variable is set to Nothing, no chart was selected before running the macro. If this
is the case, we tell the user in a message box, and then we exit the procedure.
4. Step 4 uses the For…Each statement to start looping through the series in the active charts
SeriesCollection.
5. Every chart series has a series formula. The series formula contains references back to the
spreadsheet, pointing to the cells used to create it. A typical series formula looks something
like this:
=SERIES(Sheet1!$F$6,Sheet1!$D$7:$D$10,Sheet1!$F$7:$F$10,2)
Note that there are three distinct ranges in the formula. The first range points to the series
name, the second range points to the series data labels, and the third range points to the
series data values.
Step 5 uses the Split function to parse this formula in order to extract the range for the
series data values.
6. Step 6 uses the dValues variant variable to capture the array of data values in the active
series.
7. Step 7 starts the looping through the data points in the series. It does this by setting i to
count from 1 to the number of data points in dValues. When the loop begins, i initiates with
the number 1. As the macro loops, the variable increments up one number until it reaches a
number equal to the maximum number of data points in the series.
As the macro loops, it uses i as the index number for the Points collection, effectively expos- ing
the properties for each data point. We then set the color index of the data point to match the
color index for its corresponding source cell.
8. In the last step, the macro loops back around to get the next series. After we have gone
through all the data series in the chart, the macro ends.
How to use it
The best place to store this macro is in your Personal Macro Workbook. This way, the macro is always
available to you. The Personal Macro Workbook is loaded whenever you start Excel. In the VBE Project
window, it will be named personal.xlsb.
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click personal.xlb in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code into the newly created module.
If you don’t see personal.xlb in your project window, it doesn’t exist yet. You’ll have to record a
macro, using Personal Macro Workbook as the destination.
To record the macro in your Personal Macro Workbook, select the Personal Macro Workbook option
in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-
down list box. Simply record a couple of cell clicks and then stop recording. You can discard the
recorded macro and replace it with this one.
