VBA Conditional Formatting of Charts by Series Name

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 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 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 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 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 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 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 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 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 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 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 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 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

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 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 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 Steven
Time: Thursday, July 23, 2009, 10:59 am

Hi Jon,
I’m using this code in a worksheet and it works fine except for one thing: When a field in the designated row with colors consists of only one character, it does not recognise that color in the chart. I tested this by adding an extra character and all’s fine. But as you may guess, I really need it to consist of just one character.
Any thoughts on this one? Help is much appreciated!
Thanks,
Steven.

Sub ColorBySeriesName()
Dim rPatterns As Range
Dim iSeries As Long
Dim rSeries As Range

Set rPatterns = ActiveWorkbook.Worksheets(“Schema Aangepast Ursulaklooster”).Range(“A2:A49″)
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

Comment from steven
Time: Thursday, July 23, 2009, 3:47 pm

Weird, there must be something wrong with my worksheet. I found out in the meantime that the letter ‘r’ DOES work, but the letters p/w/m/f/g do not. I did not check other letters though and these are the only single character fields in the datasheet. I’ve checked for loose spaces or any inconsistencies between the sourcefields and the chart-data but found nothing out of the ordinary.

I’m using excel 2000 and defined a lot of my own colors, but this can hardly be the cause. Besides, when I add any character to these fields, the code works perfectly. The only similarity between the dead records i can find is the fact that they’re only one character long. But since the ‘r’ does work, I’m rather at a loss as to what the problem could be. Perhaps it’s best to start from scratch. I’ll let you know if that approach works.

BTW the chart data is imported through a pivot table from MS access. Could this be at the heart of the problem? Keeping in mind that the code does work for 43 out of 48 records and there’s no inconsistencies between the fields…

Many thanks for your quick replies!!!

Comment from Steven
Time: Friday, July 24, 2009, 9:18 am

Well, this thing is keeping me awake at night. I’m sorry I keep bothering you like this, but here’s something weird you might find interesting:

If you create the following cells A1-A6 in this order: e/ei/hi/h/ri/r and add some colors and make that the color-range. Then make a sheet with two series and give them some values, with rownames corresponding to the colorrange, but not necessarily in that order (like your example on the top of the page). Now if you make a chart based upon that data and run the code, the result is that the rows that start with the same letter get the same color (at least in my 2000 version).

If you change the sorting of the color-range to ei/e/h/hi/r/ri and run the code on the same chart again, then the colors are projected as they should be. If you add any other character (or space) everythings allright no matter the order of the data. I’ve tried this several times in new sheets, workbooks and with different data and I get this result all the time.

So it looks like a consistant error, but there’s something weird. Using the data above, in the first two cells the single character comes before the double character cell, but in the subsequent cells this is the other way around. This is simillar to the odd ‘r’ in my previous post. This seems to work only for the first two cells, for if you change the order of the last two (ri/r) they get the same color again. If you mess about with it some more there are more inconsistancies for which I have no explanation (and no wish to type here :) ).

I wonder if you can recreate this phenomenon and hope you have another smart solution to this problem.
Many thanks for the time you spent on my questions!

Comment from Steven
Time: Friday, July 24, 2009, 10:19 am

Excellent!!! Works like a charm!
I’m gonna buy you a cup of coffee!!!
:D

THANKS!!

Comment from paresh
Time: Thursday, August 27, 2009, 5:40 pm

Hi Jon,

Was going through your articles on parallel coordinates. In that article you mentioned that you formatted all the lines to light grey. Did you format this line by line or is there a way to format all lines at one time – Would like to avoid VBA if possible.

paresh

Comment from paresh
Time: Saturday, August 29, 2009, 12:46 pm

Hi Jon,

Thanks.

But a parallel coordinate chart would have a large number of line and changing it one by one using F4 key would be tedious. Maybe the only alternative [ apart from using VBA ] is to use the first option in chart styles which give monchromatic graphs. Another alternative I have tried is to create chart template where say five lines have the same color and tried to use it for the new chart – works but only for five series for the sixth the color comes again.

paresh

Comment from paresh
Time: Sunday, August 30, 2009, 12:54 pm

Hi Jon

Continued my research on the subject and this is what I discovered;

a. maximum number of series in a chart is 255. This will seriously limit the use of parallel coordinates in excel as one would expect analysing hundreds of items at one time.

b. An easy way to create a line chart with one color is ;
1. create a line chart with one series
2. modify the line as per requirements; color, thickness.
3. Copy the series in the chart using control c
4. paste it as many times as you want using contol v [ the 255 limit will apply]
5. save the chart as template

In the future whenever you draw a chart you can change the chart type to the saved template — you will have a line chart with one color.

If the limit was not there, one could have visualised the use of parallel coordinates in excel with incredible scope using the filter functionality.

Anyway time for an article on phd on chart templates.

Comment from Joel Lahrman
Time: Friday, October 16, 2009, 1:43 pm

Jon, just found the code you listed above and it has been a TREMENDOUS help, thanks.

I’m looking through the conversation and I don’t believe the following question has been asked…

In addition to changing the colors of the interiors of the bars in my chart, I’d like it to be able to change the color of the label font as well. I’m putting the labels at the base of the bars. One of my bars is set to show up black, so obviously a label with black font won’t show. I’m tried variations in my code to get it to pick up the font color in addition to the background color in my rPatterns range, but so far no luck.

Any ideas on this? Thanks much, I’m far from a programmer myself but am pretty good at adapting code.

Comment from Joel Lahrman
Time: Monday, November 2, 2009, 10:36 pm

Thanks so much again!

Comment from Stacey
Time: Thursday, May 6, 2010, 6:57 pm

I was so happy to discover this code today and can see it being very useful. However, when I run a macro (ColorBySeriesName or ColorByValue) the colors that get implemented don’t always match the colors I’ve applied to my reference cells (A1:A4). Sometimes the colors are close, other times they are way off (completely different hue, or more often it seems a shade of gray). This occurs whether I use colors selected from theme or from “more colors.” I did create two custom color themes today, but I don’t see how that could affect the ColorBy macros because isn’t the vba taking the ColorIndex from the reference cells and applying that directly to the chart features?

Comment from Sri
Time: Saturday, June 5, 2010, 2:30 am

I am new to Excel VBA Programming. I have to write a VBA code which would copy the complete Chart format and style and then Replicate in other Worksheets and workbooks the same Format and style, but with different Data Source. (The Chart is defined by user manually, and i have to get the settings of this Chart).
If anyone knows please help me in completing this task!!!

‘Thanks in Advance!!!

Comment from Sri
Time: Sunday, June 6, 2010, 10:10 am

Hey Jon,

Thanks for the fast reply!!!

Currently i am doing this work manually Chart by Chart… and it takes a lot of time.

I was looking for automating this because i have almost 60 to 70 Charts in each workbook. Like this i have several workbooks to change. Currently i am working in reporting department of a company.

Comment from Karyn Orts
Time: Saturday, January 22, 2011, 1:37 am

yeah,I just thought you may possibly need to know that your blog is messed up when you view it on my iphone. I?m not positive if it has something to try and do with my telephone?s browser or your website? just saying…

Comment from Boris Parizot
Time: Thursday, February 3, 2011, 12:24 pm

I was trying to find such function in Excel for a real while and could never achieve it ! Thanks !!
However, I had the 2007 problem that the color were not properly picked-up.
Also, even though I understand almost nothing to what I am doing, I found a crosoft page describing the iColorIndex function, where they actually say you can use iColor instead, what I did and works perfectly…
Thank you again for the code !

Comment from Alexis
Time: Monday, May 9, 2011, 3:05 pm

This code is exactly what I need but I can’t get it to work. I have a chart with a list of categories that I’d like to code. The categories are in sheet “seriescolors” and each cell has been colored with the color I’d like the series. The chart is in a sheet called “exhibit”.

Dim rPatterns As Range
Dim iSeries As Long
Dim rSeries As Range

‘ Set location of category names and their colors
Set rPatterns = ActiveWorkbook.Worksheets(“seriescolors”).Range(“A1:A19″)

/*Then there’s code that generates the chart*/

With ActiveChart

For iSeries = 1 To .SeriesCollection.Count
Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, LookAt:=xlWhole)

.SeriesCollection(iSeries).Interior.ColorIndex = rSeries.Interior.ColorIndex
Next

If Not rSeries Is Nothing Then
.SeriesCollection(iSeries).Interior.ColorIndex = rSeries.Interior.ColorIndex
End If
End With

It gives me an error at “.SeriesCollection(iSeries).Interior – “Object Variable or With Block Not Set” and the “iSeries” is coming up as 1. Any ideas what might be wrong here? Let me know if I can give more detail. Thanks!!!

Comment from Leah
Time: Wednesday, January 18, 2012, 11:39 am

Jon,

I can’t thank you enough for the help on your site. I have successfully built the Gantt Chart for Repeated Tasks and now need the bar for each task to be a different color based on selections set by user. (In other words, I do not want to individually format each bar)

I’ve attempted the VBA format by Series Name as well as by Category Label and haven’t gotten anywhere. I was thinking it may be due to the layers of different series in the Gantt chart? Anyhow, here is the current code I’m working with (one Sheet named DATA and one Chart named IA IT PROJECTS):

Sub ColorBySeriesName()
Dim rPatterns As Range
Dim iSeries As Long
Dim rSeries As Range

Set rPatterns = Sheets(“DATA”).Range(“C19:C25″)
ActiveSheet.ChartObjects(“IA IT PROJECTS”).Activate
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

I had first color coded the 7 task names (AKA Ambulance 1, 2, 4, 7 from your charting tutorial) but thought maybe I should color code the associated number – neither worked.

Currently, this is my error: “Run-time error ’5′: Invalid procedure call or argument”

As you can probably guess, this is my first experience with VBA.

Thank you in advance.

Sincerely,
Leah