VBA Conditional Formatting of Charts by Series Name
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2009.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
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 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:
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)
If Not rSeries Is Nothing Then
.SeriesCollection(iSeries).Interior.ColorIndex = _
rSeries.Interior.ColorIndex
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.


To format by category label, see VBA Conditional Formatting of Charts by Category Label.
To format by value, see VBA Conditional Formatting of Charts by Value.
Possibly Related Posts:
- VBA Conditional Formatting of Charts by Category Label
- VBA Conditional Formatting of Charts by Value
- Extract Chart Data
- VBA to Split Data Range into Multiple Chart Series
- Dynamic Chart using Pivot Table and VBA
- Stack Columns In Order Of Size With VBA
- Split Data Range into Multiple Chart Series without VBA
- Update Regular Chart when Pivot Table Updates
- Excel Chart Color Update
- Interesting Links for 17 April 2009
Posted: Monday, March 3rd, 2008 under VBA.
Comments: 34
Comments
Comment from Tony
Time: Tuesday, March 4, 2008, 8:28 pm
Jon - I’m not a huge fan of the color choices, but it’s a nice trick that is pretty straightforward. Nice work!
Comment from Jon Peltier
Time: Tuesday, March 4, 2008, 8:40 pm
Tony - Thanks for the comment. The colors are the bright versions, intended for lines and markers, rather than the lighter versions intended for fills like these. It’s a snap to update the colors since I still have the VBA procedures in the workbook, but the thought of uploading a dozen or more pictures….
The trick is pretty easy, because you can much more easily change a cell color than figure out which color index to plug into the code. I can even trust my users to do that part right.
I don’t know what’s up with that code box; it keeps forgetting the indentation settings.
Comment from John Mansfield
Time: Wednesday, March 5, 2008, 12:26 am
Jon,
Each of your three recent entries on chart formatting techniques are very practical. I can see using the macros at work to insure that the chart colors remain uniform across different documents.
On another note, to keep the indentation settings for the VBA code, maybe the “pre” tag would work . .
Your code here
You can format it in your CSS template similar to any other tag . .
pre {
padding: 5px;
border: 1px solid #000000;
background-color: #FBFAF5;
font-family: verdana, georgia, times new roman, times, serif;
font-size: 11px;
color: #000000;
}
The downside is that it may not pick up the color changes within the code.
– Just thought I’d suggest it.
Comment from John Mansfield
Time: Wednesday, March 5, 2008, 12:30 am
Jon,
Sorry, it looks like I didn’t pick up the “pre” tag. Hopefully this works . .
< pre > Your code here < / pre >
Comment from Jon Peltier
Time: Wednesday, March 5, 2008, 7:09 am
John -
Thanks for the suggestion about the pre tags. I’ve actually used pre tags about since I started my first web site in 2001. Unfortunately the WodPress editor takes liberties with the code you enter, and sometimes steps on tags, especially pre and br. Grr. The code lister I use is the one Dick K uses on Daily Dose of Excel, and it seems okay, but sometimes the indents go away, and I need to figure out what to do about the garish colors.
Comment from Davie
Time: Monday, March 17, 2008, 9:33 am
Jon, I am using your code to format a graph with the option of selecting 19 colours.
It worked successfully earlier today but now has an error message of “Object Variable or With block variable not set”.
I cannot figure out what is causing this. Has anyone came across this problem before?
The code is exactly what I require.
Here is the code I used.
[vb]Sub ColorBySeriesName()
Dim rPatterns As Range
Dim iSeries As Long
Dim rSeries As Range
Set rPatterns = Sheets(”Tools”).Range(”A1:A19″)
With ActiveChart
For iSeries = 1 To .SeriesCollection.Count
Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name)
.SeriesCollection(iSeries).Interior.ColorIndex = rSeries.Interior.ColorIndex
Next
End With
End Sub[/vb]
Regards,
Davie
Comment from Jon Peltier
Time: Monday, March 17, 2008, 10:26 am
Davie - You didn’t indicate which line produced the error. The error you cited could occur if the active workbook has no sheet names “Tools”, and also if there’s no active workbook at all, or if a chart isn’t selected when the code first runs, or if there is no SeriesCollection(iSeries).
Also note that you can get formatted code in your comment by enclosing the code in VB tags, like these without spaces between [ and ]:
[ v b ]
Your code here
[ / v b ]
I’ve applied VB tags to the code in your comment above.
These [ v b ] and [ / v b ] tags no longer work.
Comment from Davie
Time: Monday, March 17, 2008, 11:08 am
Jon, the error occurs on line 10. The active workbook has a sheet “Tools”, I have selected the chart as the ActiveChart before I run the macro.
I want ultimately to run this in the formatting table macro.
Davie
Comment from Jon Peltier
Time: Monday, March 17, 2008, 11:19 am
Davie - What kind of chart is it? Line 10 is attempting to change
.SeriesCollection(iSeries).Interior.ColorIndex
but some kinds of series have no Interior (line or XY charts, for example), so this line will fail. You would have to modify the code to adjust .MarkerForegroundColorIndex, .MarkerBackgroundColorIndex, and .Border.ColorIndex for such a series.
Comment from Davie
Time: Tuesday, March 18, 2008, 4:22 am
Jon, it is a stacked column graph. I tried “MarkerBackgroundColorIndex but still got the same error message.
Davie
Comment from Davie
Time: Wednesday, March 19, 2008, 5:31 am
Jon, i recreated the graph and it is working ok. Don’t know what the problem was. Thanks for your help.
Davie
Comment from tom
Time: Wednesday, April 23, 2008, 2:15 am
hi Jon
is it possible to apply this to cells that are conditionally formatted
the color of my series are determined by other financial information therefore i use conditional formatting to assign colors
i have had no luck so far
thanks
tom
Comment from Jon Peltier
Time: Wednesday, April 23, 2008, 6:56 am
Tom - VBA only knows the base formatting for a cell, not what is applied by conditional formatting. You would have to write your formatting conditions into the code that selects the colors for the chart.
Comment from Drandon Campbell
Time: Tuesday, May 13, 2008, 1:17 pm
Jon, as usual your excel knowledge has helped me tremendously in the past. I thank you very much. I get the error “Object Variable or With block variable not set” on the line “.SeriesCollection(iSeries).Interior.ColorIndex = rSeries.Interior.ColorIndex”. I am using a Bubble Chart. Anyway I can do this same color principal on this type of chart?
Thank you.
Comment from Jon Peltier
Time: Tuesday, May 13, 2008, 2:14 pm
Drandon -
I just tested it, and the code as written works fine in a bubble chart. The only way I replicated your error in the indicated line is if the series name isn’t listed in the range where the colors are defined. Check for a typo or that your VBA range includes all the formatted cells. In other words, this fails:
Set rPatterns = ActiveSheet.Range(”A1:A4″)
if you have more than four series.
Comment from Drandon Campbell
Time: Tuesday, May 13, 2008, 2:45 pm
I have 10 Payers (Top 10 - will not change)
Here is my code -
Sub setColor()
Dim rPatterns As Range
Dim iSeries As Long
Dim rSeries As Range
Set rPatterns = Sheets(”LOOKUPS”).Range(”K51:K60″)
ActiveSheet.ChartObjects(”Chart 18″).Activate
With ActiveChart
For iSeries = 1 To .SeriesCollection.Count
Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name)
.SeriesCollection(iSeries).Interior.ColorIndex = rSeries.Interior.ColorIndex
Next
End With
End Sub
Thanks Jon.
Comment from Jon Peltier
Time: Tuesday, May 13, 2008, 5:04 pm
That’s the code. Things to check:
+ Is the correct chart being activated?
+ Is K51:K60 the correct format lookup range?
+ Does the chart have the right number of series?
+ Are the series names exactly as found in the lookup list (no unintentional spaces, etc.)?
+ Does the code fail on the first loop, or do some series get the new formatting applied?
Comment from Drandon Campbell
Time: Wednesday, May 14, 2008, 12:14 pm
There was actually two more series than I thought (two are set error bars- vertical and horizontal). I reset K60 to K62 and set the last two cells to no color. Still didn’t work - same error. I think my issue may be that my Payers actually constitute a total of 50 or so. K51:K62 are referencing the chart data (=’Bubble Graph’!B16 and so on). When I change the year the top 10 payers change. I don’t think I have enough colors for all 50 Payers :)
Oh well. Thanks a lot though.
Comment from Jon Peltier
Time: Wednesday, May 14, 2008, 12:32 pm
If a series name isn’t found, Excel can’t find a cell to select a color from. You need to trap for this error:
If Not rSeries Is Nothing Then
.SeriesCollection(iSeries).Interior.ColorIndex = rSeries.Interior.ColorIndex
End If
Comment from ariella
Time: Wednesday, August 13, 2008, 10:26 am
Jon,
I copied your example and it worked no problem. But, when i try to apply it to my sheet i get the same error as others above. Does it not work if the color reference cells are formulas, not values?
Comment from Jon Peltier
Time: Wednesday, August 13, 2008, 10:43 am
Ariella -
There are several reasons that the code may fail. It helps me to debug if you can indicate the error number and description and the line of code which is highlighted when the error occurs.
I have added the lines of code from a couple comments above, which bypasses the formatting line if the series name is absent from the lookup list.
If the lookup list is generated by formulas, you have to make sure that the formulas produce the series names exactly. A stray space character or a character with mismatched capitalization will prevent matches.
Comment from ckz
Time: Monday, September 8, 2008, 3:02 pm
Can the above code be used for pie charts?
I have a worksheet where data is entered. This data is copied to a hidden worksheet where the chart creates its datarange.
On the page the user enters the data, I have a button for the user to view the charts.
When the comandbutton is clicked - I tell the program to hide any rows with values of zeroes in the hidden worksheet so that the only information that is charted has a value.
I want to add code to assign the correct colors to each category label.
Here is my code… I am far from a programmer, and know little about the correct language to use for VBA.
Private Sub CommandButton3_Click()
Dim rng As Range
For Each rng In Worksheets(”ChartData”).Range(”VolumeRange”)
If rng.Value = 0 Then
Worksheets(”ChartData”).Rows(rng.Row & “:” & rng.Row).Hidden = True
End If
Sub ColorBySeriesName()
Dim rPatterns As Range
Dim iSeries As Long
Dim rSeries As Range
Set rPatterns = ActiveSheet.Range(”A1:A14″)
With ActiveChart
For iSeries = 1 To .SeriesCollection.Count
Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name)
If Not rSeries Is Nothing Then
.SeriesCollection(iSeries).Interior.ColorIndex = _
rSeries.Interior.ColorIndex
End If
Next
End With
End Sub
Next
Application.ScreenUpdating = False
Sheets(”Water Table”).Visible = False
Sheets(”Pie Charts”).Visible = True
Sheets(”Pie Charts”).Select
End Sub
Comment from Jon Peltier
Time: Monday, September 8, 2008, 4:05 pm
ckz -
The technique works just fine for a pie chart. But not this particular implementation. A pie chart has but a single series, plus a variety of categories. Formatting by series name will not help, but formatting by category should do what you want. See my related post VBA Conditional Formatting of Charts by Category Label.
However, you can’t just drop a sub into the middle of another sub and expect it to work. Put the sub from this site into a regular code module, and call it from byt event procedure (CommandButton3_Click) by simply typing its name in a line of code.
Then make sure you reference the appropriate sheet and range for the color lookup range and the appropriate chart.
Comment from mikael
Time: Monday, September 8, 2008, 6:41 pm
I have already created an excel tab as a template for a report, with severa charts, and I have a macro that will replicate the pages.
Now I want to write a macro to replicate the entire page, including charts.
Is there a way I can view the proerties of a chart already formated, so that I can copy the properties into a vba module to replicate the chart, without having to recreate the chart while recording a macro in order to capture the coding of the formatting that I am applying the chart being recorded?
Comment from Jon Peltier
Time: Monday, September 8, 2008, 10:09 pm
Mikael -
Make a template, that is a worksheet or workbook with all the ranges and charts the way they need to be for your report. Then simply paste the new data values into a copy of the template. The ranges (all the formulas) will update, and so will the charts.
Save the workbook as a separate Excel template (XLT file), then with the program, insert a new sheet based on the template. Step two is pasting in the data. There need not be a step three.
Trying to do this property by property in a chart is a daunting task. There are so many properties that must be considered, some properties exist for only certain chart types, or for certain axis types.
Comment from john
Time: Tuesday, October 21, 2008, 9:11 pm
I am trying your code for a number of line chart where I want the colors to be the same for each line series in Excel 2003. Your script worked for an area chart, but I needed to modify it for a line chart.
I tried to modify your code, but get an error at .SeriesCollection(iSeries).Border.ColorIndex = rSeries.Border.ColorIndex
the error is “The object doesn’t support this property or method. ”
Thanks for your help
Sub ColorBySeriesName()
Dim rPatterns As Range
Dim iSeries As Long
Dim rSeries As Range
Set rPatterns = ActiveSheet.Range(”A1:H1″)
With ActiveChart
For iSeries = 1 To .SeriesCollection.Count
Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name)
If Not rSeries Is Nothing Then
.SeriesCollection(iSeries).Border.ColorIndex = rSeries.Border.ColorIndex
End If
Next
End With
End Sub
Comment from Jon Peltier
Time: Tuesday, October 21, 2008, 11:02 pm
John -
Two problems with using rSeries.Border.ColorIndex
1. A range has a Borders property, not a Border property.
2. If a cell has more than one color among its borders, rSeries.Borders.ColorIndex returns Null, because there is no single color index that applies. You could use rSeries.Borders(xlEdgeTop).ColorIndex, but that’s a bit hard to work with. Also, it’s harder for a user to set border colors than interior colors. I would recommend you stick with the interior color and rSeries.Interior.ColorIndex
Comment from john
Time: Tuesday, October 21, 2008, 11:20 pm
Previously, you mentioned that the procedure won’t work with a line chart. What procedure would you write to change a line chart.
Davie - What kind of chart is it? Line 10 is attempting to change
.SeriesCollection(iSeries).Interior.ColorIndex
but some kinds of series have no Interior (line or XY charts, for example), so this line will fail. You would have to modify the code to adjust .MarkerForegroundColorIndex, .MarkerBackgroundColorIndex, and .Border.ColorIndex for such a series.
Comment from Jon Peltier
Time: Tuesday, October 21, 2008, 11:26 pm
John -
Here is what I would use to color the lines and markers in a line chart:
Sub ColorBySeriesName()
Dim rPatterns As Range
Dim iSeries As Long
Dim rSeries As Range
Dim iColorIndex As Long
Set rPatterns = ActiveSheet.Range("A1:H1")
With ActiveChart
For iSeries = 1 To .SeriesCollection.Count
Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name)
If Not rSeries Is Nothing Then
iColorIndex = rSeries.Interior.ColorIndex
With .SeriesCollection(iSeries)
.Border.ColorIndex = iColorIndex
.MarkerForegroundColorIndex = iColorIndex
.MarkerBackgroundColorIndex = iColorIndex
End With
End If
Next
End With
End Sub
Comment from john
Time: Wednesday, October 22, 2008, 7:55 am
Thanks for your help with this.
I am getting an error at the line “iColorIndex = rSeries.Border.ColorIndex”. “Object does not support property or method.”
Comment from Jon Peltier
Time: Wednesday, October 22, 2008, 9:10 am
Doh! That should be
iColorIndex = rSeries.Interior.ColorIndex
Comment from john
Time: Wednesday, October 22, 2008, 2:17 pm
Thanks for your help.
Comment from john
Time: Wednesday, October 22, 2008, 2:56 pm
Another query, I would like the markerstyle to vary between the series on a particular chart like the color varies, but be constant across charts for each series.
Comment from Jon Peltier
Time: Wednesday, October 22, 2008, 7:05 pm
You need to include the code to manipulatee the marker style:
Sub ColorAndMarkerBySeriesName()
Dim rPatterns As Range
Dim iSeries As Long
Dim rSeries As Range
Dim iColorIndex As Long
Set rPatterns = ActiveSheet.Range("A1:A4")
With ActiveChart
For iSeries = 1 To .SeriesCollection.Count
Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name)
If Not rSeries Is Nothing Then
iColorIndex = rSeries.Interior.ColorIndex
With .SeriesCollection(iSeries)
.Border.ColorIndex = iColorIndex
.MarkerForegroundColorIndex = iColorIndex
.MarkerBackgroundColorIndex = iColorIndex
End With
Select Case LCase$(rSeries.Offset(, 1).Value)
Case "square"
.SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleSquare
Case "circle"
.SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleCircle
Case "triangle"
.SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleTriangle
Case "diamond"
.SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleDiamond
End Select
End If
Next
End With
End Sub























Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.