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.
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 chart with several series, and the series may be in any order. However, you want to color a category according to its name, so that Halliburton always is colored red and Enron is always blue.
The following protocol allows you to color the series in a chart according to colors you’ve reserved for each series. The range below illustrates the data: range A1:A4 contains a list of the series names, with each cell filled with the desired color for that series’ data in a chart. A6:E10 contains data for Chart 1, and A12:E16 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.)
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 seriess, based on their names.
Excel 2003 and earlier:
Sub ColorBySeriesName()
Dim rPatterns As Range
Dim iSeries As Long
Dim rSeries As Range
Set rPatterns = ActiveSheet.Range("A1:A4")
With ActiveChart
For iSeries = 1 To .SeriesCollection.Count
Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, _
LookAt:=xlWhole)
If Not rSeries Is Nothing Then
.SeriesCollection(iSeries).Interior.ColorIndex = _
rSeries.Interior.ColorIndex
End If
Next
End With
End Sub
Excel 2007 and later:
Sub ColorBySeriesName()
Dim rPatterns As Range
Dim iSeries As Long
Dim rSeries As Range
Set rPatterns = ActiveSheet.Range("A1:A4")
With ActiveChart
For iSeries = 1 To .SeriesCollection.Count
Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, _
LookAt:=xlWhole)
If Not rSeries Is Nothing Then
.SeriesCollection(iSeries).Format.Fill.ForeColor.RGB = _
rSeries.Interior.Color
End If
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 series names and the colored table in A1:A4.
Related Articles about VBA Conditional Formatting of Charts
- VBA Conditional Formatting of Charts by Value
- VBA Conditional Formatting of Charts by Category Label
- VBA Conditional Formatting of Charts by Value and Label
- Conditional Formatting of Lines in an Excel Line Chart Using VBA
