In Conditional Formatting of Excel Charts I showed how to simulate conditionally formatting in your charts charts. The trick is to set up ranges containing formulas for each of the conditions, so that if the condition for that range is met, the value will be plotted in the appropriately formatted chart series. Otherwise it doesn’t appear in the chart, but a differently formatted point will.
In most cases the technique in the cited article is the best approach, since it does not use VBA and it updates with the speed of Excel formulas. Sometimes, though, you need a VBA solution.
Prepare for VBA Formatting
In a worksheet named “ColorSheet”, I have set up a range that has row headers corresponding to the category labels I expect to encounter, and column headers corresponding to values I want to use as cut-offs for conditional formatting. The cell at the intersection of the category label row and value column is formatted in the way I want the chart’s data to be formatted. For example, a point with a category label of Alpha will be some shade of blue, while a point labeled Eta will be orange. The shade of the color used is lighter for smaller values and darker for larger values. I have included a label “other” in case an unexpected label is found, and a value of “above” in case the maximum in the table is exceeded. The light shades might be too light, and in any case, shades of color shouldn’t be expected to provide much resolution when encoding information.
Naturally, your labels and values will be different, and in fact you may need more complicated algorithms in the code.
I’ve named this region “ColorRange”. Note that this name appears in the Name Box above column A when the range is selected.
Here is my data and chart. The bars have the default formatting for series 1.
Apply Formatting by Running the VBA Code
Here is the chart after running the FormatPointByCategoryAndValue procedure.
After changing the values and rerunning the procedure, the chart’s bars keep their color, though the shades have become lighter or darker. The light green for Gamma is too light to distinguish from gray, and almost too light to see at all. I should probably go back and darken all of the lighter shades.
Now all of the data has changed, and in fact, more data is plotted in the chart. The code still works as expected. Note the Omega series, which is colored gray because that label was not included in the original color table.
The VBA Procedure
The code is not too complicated. After the declarations (Dim and Consts) the first thing that the code does is abort if the user has not selected a chart (a little extra effort is always well-spent if it that a run time error). There is a line of code which, if uncommented, will remind the user to select a chart by showing this dialog:
Then the range containing the colors is identified and its values put into an array to make the values easier to work with.
The first series of the active chart is defined as the series we are formatting. The category labels (XValues) and values (Values) are put into arrays, also for ease of processing.
The code then looks at each point’s value and label, to determine which cell has the desired formatting. The rows and columns are looped starting at 2, since the first of each contains an irrelevant label. The looping stops one count before the end. If a match is not found, the loop counter points to the last row or column.
Finally the point is given the same fill color as the matching cell in the color table. Note that there are two sets of syntax, one for Classic Excel (2003 and earlier) and the other for Neo Excel (2007 and later). I’ve commented out the 2003 syntax, because I now use 2010 for most of my outward-facing work.
Sub FormatPointByCategoryAndValue()
Dim rColor As Range
Dim vColor As Variant
Dim srsColor As Series
Dim iRow As Long
Dim iCol As Long
Dim iPoint As Long
Dim vCategories As Variant
Dim vValues As Variant
Const sColorSheetName As String = "ColorSheet"
Const sColorRangeName As String = "ColorRange"
If ActiveChart Is Nothing Then
' uncomment following line to alert user
'MsgBox "Select a chart and try again.", vbExclamation, _
"No Active Chart"
GoTo ExitHere
End If
Set rColor = Worksheets(sColorSheetName).Range(sColorRangeName)
vColor = rColor.Value
Set srsColor = ActiveChart.SeriesCollection(1)
With srsColor
vCategories = .XValues
vValues = .Values
' cycle through points
For iPoint = 1 To .Points.Count
' find category (row)
For iRow = LBound(vColor, 1) + 1 To UBound(vColor, 1) - 1
If vCategories(iPoint) = vColor(iRow, 1) Then Exit For
Next
' find value (column)
For iCol = LBound(vColor, 2) + 1 To UBound(vColor, 2) - 1
If vValues(iPoint) <= vColor(1, iCol) Then Exit For
Next
' apply color of identified cell to given point
' Excel 2007+ syntax
.Points(iPoint).Format.Fill.ForeColor.RGB = _
rColor.Cells(iRow, iCol).Interior.Color
' Excel 2003- syntax
'.Points(iPoint).Interior.ColorIndex = _
rColor.Cells(iRow, iCol).Interior.ColorIndex
Next
End With
ExitHere:
End Sub
To run the procedure, select the chart, then press Alt+F8, select FormatPointByCategoryAndValue, and click Run.
Here are some closely related articles from the archives:
To format by point value, see VBA Conditional Formatting of Charts by Value.
To format by category label, see VBA Conditional Formatting of Charts by Category Label.
To format by series name, see VBA Conditional Formatting of Charts by Series Name.
