VBA Conditional Formatting of Charts by Value

March 2008
S	M	T	W	T	F	S
« Feb		Apr »
	1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31

by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2008. All rights reserved.

I've got a couple of tutorial pages on my web site that deal with conditionally formatted 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 range contains the value to plot, otherwise it contains #N/A, which isn't plotted in a line or XY chart, or just a blank or zero, which is plotted as a zero-thickness bar or column in a bar or column chart. You can read about them here:

Simple Conditional Chart Example 1 - Simple Conditional Chart Example 2

As with conditional formatting in the worksheet, the formatting you can achieve is limited only by your ability to construct formulas. Of course, in the chart, you are not limited to three conditional formats as you are in the worksheet (in Excel 2003 at least; the conditional formatting enhancements in Excel 2007 are mind-boggling).

Although I prefer to use the formulaic approach above, sometimes it makes more sense to use VBA to apply formats. Suppose you have a column chart, and the values may be in any order. However, you want to color a column according to its value, so that small values always are colored red and large values are always green.

The following protocol allows you to color the points in a series according to colors you've reserved for certain values. The range below illustrates the data: range A1:A4 contains a list of the categories, with each cell filled with the desired color for that value in a chart; actually, the procedure will use the color in the cell with the smallest value greater than or equal to the point's value. A6:B10 contains data for Chart 1, and A12:B16 contains data for Chart 2. (The colors in A1:A4 were defined using the Color Brewer utility described in my earlier post, Using Colors in Excel Charts.)

	A	B
1	1
2	2
3	3
4	4
5
6		Chart 1
7	Comcast	4
8	Brown & Williamson	3
9	Enron	2
10	Halliburton	1
11
12		Chart 2
13	Halliburton	3.5
14	Brown & Williamson	3
15	Comcast	2.75
16	Enron	1.5

The charts made from the two ranges are not remarkable.

There is a simple VBA procedure that will apply the colors from the designated range to the appropriate points, based on their categories:

PLAIN TEXT

Visual Basic:

Sub ColorByValue()
Dim rPatterns As Range
Dim iPattern As Long
Dim vPatterns As Variant
Dim iPoint As Long
Dim vValues As Variant
Dim rValue As Range
Set rPatterns = ActiveSheet.Range("A1:A4")
vPatterns = rPatterns.Value
With ActiveChart.SeriesCollection(1)
vValues = .Values
For iPoint = 1 To UBound(vValues)
For iPattern = 1 To UBound(vPatterns)
If vValues(iPoint) <= vPatterns(iPattern, 1) Then
.Points(iPoint).Interior.ColorIndex = _
rPatterns.Cells(iPattern, 1).Interior.ColorIndex
Exit For
End If
Next
Next
End With
End Sub

Select a chart, and run the procedure above (press Alt+F8 to bring up the macro dialog, select the procedure, and click Run). The bars will change color according to their values and the colored table in A1:A4.