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 sorted column chart, and the categories 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 points in a series according to colors you’ve reserved for certain categories. 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 category’s bar in a chart. 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.)
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.
Excel 2003 and earlier:
Sub ColorByCategoryLabel()
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rCategory As Range
Set rPatterns = ActiveSheet.Range("A1:A4")
With ActiveChart.SeriesCollection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rCategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex
Next
End With
End Sub
Excel 2007 and later:
Sub ColorByCategoryLabel()
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rCategory As Range
Set rPatterns = ActiveSheet.Range("A1:A4")
With ActiveChart.SeriesCollection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rCategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Format.Fill.ForeColor.RGB = rCategory.Interior.Color
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 category names and the colored table in A1:A4.
To format by value, see VBA Conditional Formatting of Charts by Value.
To format by series name, see VBA Conditional Formatting of Charts by Series Name.
I have a problem and I was hoping yo could help me. I have an Excel stacked bar chart I’m using as a Gantt diagram. I need to create some sort of tracking of the activities that are either taking place, done or not even started.
I used some conditional formatting so as to give the adequate format to each of the cells in the data source for the chart. I then tried to use your VBA subroutine to bring this format into the chart but I don’t really understand the commands in the VBA code and what would really help me would be to give the format to each of the category labels in the Y axis or something like that.
Is there a way you could explain how the code works? or help me with some variation of the code that does what i need?
Hi Pablo -
VBA cannot directly pick up conditional formatting from the conditonally formatted cells. You need to do some intricate VBA machinations to determine which condition has been satisfied, then extract the corresponding format from the cell.
Chip Pearson talks about conditional formatting here:
http://www.cpearson.com/excel/cformatting.htm
and he has the VBA required to extract the applied format here:
http://www.cpearson.com/excel/CFColors.htm
Hi,
I have tried this macro but unfortunately, I have an error message(object variable or with block variable not set) at line 8, does anyone know how to fix it please ? Thanks a lot.
Set rPatterns = ActiveSheet.Range(“A1:A4″)
Have you selected a chart? The procedure needs an active chart.
Im trying to use this for a gantt chart as well, How do i set this color autoformatting to apply this to a multi step bar graph. It auto sets the color to the first part but not second part.
J –
If you are formatting more than one series in a chart you have to change this line:
to account for all series you need to format. The adjusted routine would look like:
Sub ColorByCategoryLabel() Dim rPatterns As Range Dim iCategory As Long Dim vCategories As Variant Dim rCategory As Range Dim iSeries As Long Set rPatterns = ActiveSheet.Range("A1:A4") For iSeries = 1 To 5 '' select appropriate start and finish numbers With ActiveChart.SeriesCollection(iSeries) vCategories = .XValues For iCategory = 1 To UBound(vCategories) Set rCategory = rPatterns.Find(What:=vCategories(iCategory)) .Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex Next End With Next End SubThis would be so extremely useful for me on a project… but, I keep having issues with the line
.Points(iCategory.Interior.ColorIndex=rCategory.Interior.ColorIndex
I have been messing with this and trying to find a way to make it work with no success… Any help would be greatly appreciated!
(Oh, and I am just using the standard Excel colors… lol, as icky as they may be… and not the ColorBrewer colors… Not sure if that is what is causing my issue or not…)
Thank you in advance!!
OH, and to add to my comment….
When I try this on another Active Chart it just refers me to the first line in the debugger.
And my charts are all on one worksheet, and my data is on another…
I’m not sure what the problem is, but I have a couple ideas. The colors in your palette have no bearing.
1. “And my charts are all on one worksheet, and my data is on another…”
Assuming the worksheet with the colored ranges is named “My Colors”…
Sub ColorByCategoryLabel() Dim rPatterns As Range Dim iCategory As Long Dim vCategories As Variant Dim rCategory As Range Set rPatterns = ActiveWorkbook.Worksheets("My Colors").Range("A1:A4") With ActiveChart.SeriesCollection(1) vCategories = .XValues For iCategory = 1 To UBound(vCategories) Set rCategory = rPatterns.Find(What:=vCategories(iCategory)) .Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex Next End With End Sub2. “.Points(iCategory).Interior.ColorIndex”
This line only works for a chart type in which each point has an interior, like a column or bar chart, or a pie chart. In a line chart, you cannot format the markers this way. You have to use:
.Points(iCategory).MarkerForegroundColorIndex
.Points(iCategory).MarkerBackgroundColorIndex
Thank you so much for your help!!! I ended up going ahead and attaching it to a button, with an addition that allows it to work on all the charts on the worksheet (I had 6 on the same page….) Below is the code (just in case someone else is in the same boat!) Love all your posts and appreciate you putting them up!!
Sub ColorByCategoryLabel() ' Set chart category colors in all charts on active sheet by category name ' according to color table on Sheet 1 ' Set chart category colors Dim oChart As ChartObject Dim rPatterns As Range Dim iCategory As Long Dim vCategories As Variant Dim rCategory As Range ' Set location of category names and their colors Set rPatterns = ActiveWorkbook.Worksheets("Sheet 1").Range("B1:B26") ' Loop through all charts on main page For Each oCht In ActiveSheet.ChartObjectsoCht.ActivateWith ActiveChart.SeriesCollection(1)With oCht.Chart.SeriesCollection(1) vCategories = .XValues ' Loop through all categories of chart, ' find and set color of matching category For iCategory = 1 To UBound(vCategories) Set rCategory = rPatterns.Find(What:=vCategories(iCategory)) .Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex Next End With Next End SubHi LEM -
Thanks for posting this. I’ve made one minor change to prevent seizures in those with a low threshold.
I tried the code above from the previous writer.
Sub ColorByCategoryLabel() Dim oChart As ChartObject Dim rPatterns As Range Dim iCategory As Long Dim vCategories As Variant Dim rCategory As Range Set rPatterns = ActiveWorkbook.Worksheets("Sheet 1").Range("A1:C1") ' Loop through all charts on main page For Each oCht In ActiveSheet.ChartObjects With oCht.Chart.SeriesCollection(1) vCategories = .XValues ' Loop through all categories of chart, ' find and set color of matching category For iCategory = 1 To UBound(vCategories) Set rCategory = rPatterns.Find(What:=vCategories(iCategory)) .Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex Next End With Next End SubMy data is on Sheet 1 and the actual chart object is on Sheet 2. Do I need to activate Sheet 2 or call out the names of the objects?
Thank you.
I think this will do it:
Sub ColorByCategoryLabel() Dim oChart As ChartObject Dim rPatterns As Range Dim iCategory As Long Dim vCategories As Variant Dim rCategory As Range Set rPatterns = ActiveWorkbook.Worksheets("Sheet 1").Range("A1:C1") ' Loop through all charts on main page For Each oCht In ActiveWorkbook.Worksheets("Sheet 2").ChartObjects With oCht.Chart.SeriesCollection(1) vCategories = .XValues ' Loop through all categories of chart, ' find and set color of matching category For iCategory = 1 To UBound(vCategories) Set rCategory = rPatterns.Find(What:=vCategories(iCategory)) .Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex Next End With Next End SubI find it best to reference everything, starting with the worksheet.
I decided to use the original code written three times – I had three ranges on Sheet 1 and three charts on sheet 2.
When I execute the macro, a window with each chart object appears quickly as it is changing colors and then takes me to the page with the charts.
I was able to print screen what it happening. If there is a way to attach an item I would send the screen shot.
Is there a way to control the activity so the user does not see this happening? It is as if the three graphs become objects then put back into the sheet.
Sheet 2 is hidden until the macro is executed from a sheet 3. Not sure if that is what is making a difference.
thanks,
ckz
Not sure if viewing the actual code helps… maybe there is a more efficient way of doing this.
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rCategory As Range
Set rPatterns = Worksheets(“sheet 1″).Range(“range1″)
Worksheets(“sheet 2″).ChartObjects(“chart1″).Activate
With ActiveChart.SeriesCollection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rCategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex
Next
End With
Set rPatterns = Worksheets(“sheet 1″).Range(“range2″)
Worksheets(“sheet 2″).ChartObjects(“chart2″).Activate
With ActiveChart.SeriesCollection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rCategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex
Next
End With
Set rPatterns = Worksheets(“sheet 1″).Range(“range3″)
Worksheets(“sheet 2″).ChartObjects(“chart3″).Activate
With ActiveChart.SeriesCollection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rCategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex
Next
End With
Application.ScreenUpdating = False
Sheets(“sheet 3″).Visible = False
Sheets(“sheet 2″).Visible = True
Sheets(“sheet 2″).Select
End Sub
CKZ -
Don’t activate the charts before editing them. This is the flashing that the observer sees.
Replace this:
Worksheets(“sheet 2″).ChartObjects(“chart1″).Activate
With ActiveChart.SeriesCollection(1)
with this:
With Worksheets(“sheet 2″).ChartObjects(“chart1″).Chart.SeriesCollection(1)
Then when the code finishes, it is still at the sheet that was active when you started it.
perfect. thanks for the quick response.
ckz
So, I am back again!
This code has been working for me in three different workbooks, but on this new worksheet I am having trouble again with the following line:
.Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex
I am just using it on pie and column charts, and I have no idea what to troubleshoot next. Any suggestions?
Nevermind!!! LOL… you would never believe, I have been spending so much time on this and my problem was a trailing space… when the color list and source data did not much up it was sending me that error. Thanks though!!
You didn’t quote the error message.
You need to make sure that rCategory exists and that Points(iCategory) can be manipulated. Set a breakpoint at the line that gives you an error. In the Immediate window, type this line and press Enter:
?rCategory.Address
If that works, type this and press Enter:
?oCht.Chart.SeriesCollection(1).Points.Count
Then try
?oCht.Chart.SeriesCollection(1).Points(iCategory).Interior.ColorIndex
The answers may suggest some form of solution.
Hi Jon!
I wonder if there is a way of displaying chart labels conditionally? I’m dealing with dynamic charts and it displays one set of data at a time, using comboboxes and option buttons. The series overlap so the user won’t notice position changes, but I can’t get the labels to be shown only when the series are showing – I get multiple N/As on the bottom of the plot area. Do you know of any way to work around this?
Thank you!
If you’re seeing #N/A for data calculated by formulas, it means you’re using a column or bar chart, and these chart types plot #N/A as zero. Instead of NA() in the formula, use “”. This is also plotted as zero, and the label also shows “”.
Yes, it’s a bar chart. I tried this, but instead of #N/A it now shows zeroes!
What do the formulas look like?
Jon Peltier
i want to put series color(different solid color to fill 4 series) could you show me the VBA code please
Nong -
Do you want this: VBA Conditional Formatting of Charts by Series Name?
Hi Jon!
Your code all seem so simple, and of course it leads to thinking whether such as simple set up could do even more?
I was wondering whether in line charts, there would be a way to conditionally choose the Forgreound and background colours as you explained but also the marker type by using a 2 column condition range rather than one
The first column would be as above with the categories and the colours whilst the second column would have the code for the marker such as ” .MarkerStyle = xlDiamond”?
Just wondering….
I do not believe that any one of your “tricks has not been used or found a use as yet, sometime as a composite, sometimes just as is. Thanks a million!
Danièle
I actually use this approach to change markers in the “lightly documented” Dynamic Control Chart, which is available only as a zipped workbook. Don’t use this as a real control chart: I used it as an exercise in VBA, before I was fluent in the ways of Statistical Process Control.
Thanks!
I have been going through the code, and as usual, learning more than I bargained for. You mention that it should not be used as a real control chart? For a neophyte like me, it seemed pretty much a real control chart? So what is missing, or incorrect in the approach?
On another point, despite having found where all the titles are coded in the macro, and seeing the chart correctly labelled on opening the file with all the correct axis titles, I do not understand why when running the macro, the axis titles remain simply “Axis Title” and do not update with the cell as coded?
The series names on opening the file came as “) UCL (37.4″ but when running the macro they appeared correctly as “UCL (37.4)”. Just odd! I am currently using excel 2003. No big problem for what I was after, but I am just curious….
Many Thanks,
Danièle
In a true control chart, you have to determine your distribution parameters (mean and standard deviation), then lock them in as control limits in the chart. In my example, it implies that these limits are to be recalculated after every subsequent measurement.
Labels sometimes work in a funny way. I’ve sometimes seen the last character, the close parenthesis in your example, displayed in front of the first. Whenever I look closely to try to figure it out, it seems to go away. I don’t know what it is.
Ok, I figured out what was going wrong with the Axis titles…by checking how the Chart Title was coded.
I changed the following lines
With .AxisTitle
.AutoScaleFont = False
.Text = “=Sheet1!R2C2″
to
.Text = “=’” & ActiveSheet.Name & “‘!R2C2″
I first tried
.Text = “=’Sheet1′!R2C2″, just adding ” ‘ ” before and after Sheet1, but that did not work because Sheet1′s name was “Control Chart”.
For the labels odd behaviour… it does not appear any more. One elusive little glitch!
Today I have realised that I am still not capable of writing code efficiently, but just as for a language, I am starting to understand more and more!
For the control charts I was too interested in the code! As the data changed each time, the changes in the graphs were each time based on the new distribution parameters.
Thanks again for pointing out that as a control chart for a given distribution, changing the parameters each time a new value is added to the distribution would be incorrect.
Thanks!
Danièle
I’m having problem with the code and I’m getting an error message on the following line:
.Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex
I’m using black and white patterns for a column chart and there are 25 series. What should I do to fix this error?
Thanks!
The code I posted does not take into account of any pattern in the cells. You would need to capture the patterns and the two colors that make up the patter:
.Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex
.Points(iCategory).Interior.PatternColorIndex = rCategory.Interior.PatternColorIndex
.Points(iCategory).Interior.Pattern = rCategory.Interior.Pattern
Note: I did not test this code.
Hi Jon,
Many thanks for your excellent posts, they have been very helpfull to me. The code works perfectly on simple charts. However, I am trying to use it in a pivot-chart and it does not work properly. I get an error 91 message saying something like: “the objectvariable or blockvariable With is not designated”. (This might be a very poor translation since my excel 2000 is a dutch language version, unfortunately.) The error is in this line:
.Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex
I am not very good with code so I am at a loss as how to fix it. Is there any chance you could help me out with this?
Thanks!
Steven.
If the label isn’t found in the color range, then rCategory is nothing, so the error comes from the part after the “=”. If you have not selected a chart, thenthe part in front of the “+” would cause a problem, but this error should occur a few lines higher, at “With ActiveChart.SeriesCollection(1)”.
Thanks for your quick reply Jon. I’m afraid I’m no further with this problem though. I tried it with your sample data on the top of this page and the same thing happens. If you try to merge chart 1 & 2 in a bar chart, then only the first serie (chart 1) receives the defined colors. The second serie gets the standard excel color, although the labels refer to the exact same colum.
I’m sorry if I’m asking stupid questions here but I’m still relatively new at this stuff and rather intimidated by VBA script. I would be extremely happy if I can get this to work though. Any chance of a more noob-proof explanation?
Thanks a million!!
Steven.
The code was written for one chart, the active chart. However, the following will make it work for all charts on the activesheet, and no chart needs to be selected:
Sub ColorByCategoryLabel() Dim rPatterns As Range Dim iCategory As Long Dim vCategories As Variant Dim rCategory As Range Dim chtob As ChartObject Set rPatterns = ActiveSheet.Range("A1:A4") For Each chtob In ActiveSheet.ChartObjects With chtob.Chart.SeriesCollection(1) vCategories = .XValues For iCategory = 1 To UBound(vCategories) Set rCategory = rPatterns.Find(What:=vCategories(iCategory)) .Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex Next End With Next End Sub
Thanks for all your help Jon, I got the thing working!!
I found what i needed on your post “VBA Conditional Formatting of Charts by Series Name”. But the last code was very usefull as well!
Hi Jon,
I am trying to use this last version of the code (where no chart needs to be selected) and I keep getting the following error message:
Object Variable or With block variable not set
I am trying to use it in a spreadsheet identical to the one in your example so no additional complications there. Could you please point me towards the solution?
Thanks,
Jorge
Jorge -
In the page you’ve cited, the chart has to be selected. Note the line:
With ActiveChart.SeriesCollection(1)
Hi Jon,
thanks for the quick response. The code that I am using is:
Sub ColorByCategoryLabel() Dim rPatterns As Range Dim iCategory As Long Dim vCategories As Variant Dim rCategory As Range Dim chtob As ChartObject Set rPatterns = ActiveSheet.Range("A1:A4") For Each chtob In ActiveSheet.ChartObjects With chtob.Chart.SeriesCollection(1) vCategories = .XValues For iCategory = 1 To UBound(vCategories) Set rCategory = rPatterns.Find(What:=vCategories(iCategory)) .Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex Next End With Next End SubWhich I’ve copied and pasted in VB. I thought with this code you did not need to have any charts selected.
Despite that I still get the same error:
Run time error ’91′ Object Variable or With block variable not set
I am sure this must be an obvious mistake on my part but for the life of me I cannot see it. Could you please help me out?
Cheers
Jorge
Jorge -
I see what you were saying, I just didn’t have any code before.
You may get this error because rCategory is undefined, for example, if the category label is not present in the rPatterns range. Make sure everything is spelled the same, including leading and trailing blanks.
I’m trying to adapt this to a pivot chart that i’m making, so i’ve made just a few minor changes, which i hope were correct, but i’m getting the Run time error ’91′ Object variable with block variable not set, but when i debug i’m getting it for the line “.Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex”
any ideas?
Sub ColorByCategoryLabel()
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rCategory As Range
Dim chtob As ChartObject
Set rPatterns = ActiveSheet.Range(“H6:H7″)
For Each chtob In ActiveSheet.ChartObjects
With chtob.Chart.SeriesCollection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rCategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex
Next
End With
Next
End Sub
Tyler -
Make sure vCategories(iCategory) (the ith X axis label) actually exists in the rPatterns range.
Hello John,
Great site with some superb ideas, examples and resources – thank you!
I’m trying to modify the gradient fill colors of single data points on a bar charts (Excel 2007) and came across this page while stuck and googling for help! I’m not (yet) taking the colors from a worksheet range as in your example; just setting them in the code.
I need to alter different child properties of .interior, not the .colorindex property. I’ve waded through the object model and help file and tried all kind of syntaxes, but can’t get at the gradient without raising an error such as “Run-time error ’1004′: Application-defined or Object-defined error” or “Run-time error ’438′: Object doesn’t support this property or method”. Recording a macro of my setting this manually doesn’t help me since Excel does not record any of these steps in VBA!!
I have tried the following:
.Interior.Gradient.InteriorGradientStop(1).GradientStopColorIndex= RGB(a,b,c)
.Interior.InteriorGradientStop(1).GradientStopColorIndex= RGB(a,b,c)
.Interior.Gradient.Colorstops(1).ColorIndex = RGB(a,b,c)
(all the above with ActiveChart.SeriesCollection(1).Points(1) either referenced or selected)
Even .Gradient.ColorStops.Clear fails!
Note that the bar chart I’m refering to already has gradient fills defined manually.
I’m sure this is something simple, but I just can’t get it!
Any help much appreciated.
Paul
Paul -
I don’t spend much time on visual effects like gradients. They don’t add to the understanding of the chart’s information, and often they detract and distract from its message.
The object model for formatting of charts and shapes in Excel 2007 is not easy: the help is lacking in helpful examples, and the lack of macro recording removes a powerful tool from our arsenal. The recording functionality returns in 2010, which is helpful for routine formatting (though legacy code from 2003 still does the trick).
John,
I came upon this page with a similar problem than Paul. My boos likes his bar charts with a vertical gradient (blue-white-blue) and the bar with the name of the company painted red. I usually do it by simply painting the bar manually. Latelly, I have to paint about 60 graphs a day, so I decided to look for a better solution. I already use the following code to paint all bars the way he likes it:
Set cht = ActiveChart
Set ser = cht.SeriesCollection(1)
ser.Format.Fill.TwoColorGradient msoGradientVertical, 4
ser.Format.Fill.ForeColor.RGB = RGB(255, 255, 255)
ser.Format.Fill.BackColor.ObjectThemeColor = msoThemeColorAccent1
But I also need to paint the company bar red. I tried using your code, with the refered cell painted with a gradient pattern, but the result is all cell painted blue and the comapny cell complately blank (it’s painted red in the cells). Any ideas?
I thought i could refer to the category label without merging it to a worksheet and use code to paint it, like i did to paint all of them gradient. Can i do it using conditional to?
Thanks in advance
Sorry, Forgot to paste the beginning of my code
Dim cht As Chart
Dim ser As Series
Set cht = ActiveChart
Set ser = cht.SeriesCollection(1)
ser.Format.Fill.TwoColorGradient msoGradientVertical, 4
ser.Format.Fill.ForeColor.RGB = RGB(255, 255, 255)
ser.Format.Fill.BackColor.ObjectThemeColor = msoThemeColorAccent1
Felipe -
As I told Paul, I don’t spend a lot of time with gradient fills and other excess formatting. I know they make the boss happy, but they add nothing to the display itself. You’ll have to figure out the formatting yourself.
This would work great for my reports but I can’t get the module to work.
I have to create several pie charts and a continually have to reformat the charts so that all the colors match for each customer. I copied and pasted the VBA procedure that you initially posted on the top of the page, however, I keep getting the message “Object Variable or with block variable not set”. What am I doing wrong? I have the data all in one sheet.
Any help would be greatly appreciated.
Sandy – What line of code is highlighted? That should give a strong hint about which object or variable is not defined.
Hi Jon,
Your solution and the community contributions are great.
I understand this solution will work for Bar Charts and accrdingly I have been able to get the expected results. However, when trying to apply the same to a Bar & Line Chart, the color coding change on the “bars” is not happening.
I am no expert on writting Macros with VB; the code I have “replicated” from the discussions in this blog is:
‘ Code_start
Sub ChangeColorsBar()
‘ Set chart category colors in all charts on active sheet by category name
‘ according to color table on sheet “MyColors”
‘ Category names should be in range D2:D31
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rCategory As Range
Dim chtob As ChartObject
‘ Set location of category names and their colors
Set rPatterns = ActiveWorkbook.Worksheets(“MyColors”).Range(“D2:D32″)
‘ Loop through all charts on main page
For Each chtob In ActiveSheet.ChartObjects
With chtob.Chart.SeriesCollection(1)
vCategories = .XValues
‘ Loop through all categories of chart,
‘ find and set color of matching category
For iCategory = 1 To UBound(vCategories)
Set rCategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex
Next
End With
Next
End Sub
‘Code_end
(Note: I added the ‘Code_start and ‘Code_end in the above for clarity..).
Could you please advise what has to be changed to get this Macro working? I have some 28 similar graphs that must be changed. By the way, I am using MS Excel 2007
Many thanks and best regards
Hi Jon,
I thought it best to include an example case.
Please see http://www.scribd.com/doc/47157058
Thank you again for your help. Kind regards
Daniel -
There are a number of reasons why this routine may not work as expected, including:
1. The category labels in the chart may not be spelled the same way as the labels in the lookup table.
2. The code says “With chtob.Chart.SeriesCollection(1)” but the bar series may not be the first series in the chart.
Thanks again Jon for pointing me in the right direction.
As you refer, I tried plotting the variables in order: 1st the Bars, then the lines. This allows me to modify the colors of the with the above code, but would be forced to comply with said order. Plotting “Lines” before “Bars” and then executing the above code does not work.
Can you advise how/where to adjust the above code so that regardless of the plotting order, the code would work? Alternatively, can you advise how one can determine what “SeriesCollection()” the “Bars” have been assigned or if their is such a thing as “SeriesCollection.Name” that I can make reference to to in the code, for it to work, regardles of the plotting order?
I appreciate your interest and assitance.
Kind regards
Hi Jon. I found the answers to my questions…
SeriesCollection(index): index is the sequential number of the series or I can replace the “index” with the series “Name”….
So. I’ll have to know the “name” of the series to resolve, regardless of the sequential order of the series…
Again many thanks for pointing me in the right direction.
Daniel -
Try something like this:
Hi Jon,
I am actively using the many formatting examples you provide, the following in particular:
Sub AllColorByCategoryLabel ()
‘ Set chart colors in all charts on active sheet by category name
‘ according to color table on Sheet 1
‘ Set chart category colors
Dim oChart as ChartObject
Dim rPatterns as Range
Dim iCategory as Long
Dim vCategories as Variant
Dim rCategory as Range
‘ Set location of category names and their colors
Set rPatterns = ActiveWorkbook.Worksheets(“MyColors”).Range)(“H2:H89″)
‘ Loop through all charts on main page
For Each oCht In ActiveSheet.ChartObjects
oCht.Activate
With ActivateChart.SeriesCollection(1)
With oCht.Chart.SeriesCollection(1)
vCategories = .XValues
‘ Loop through all categories of chart, find and set color of matching category
For iCategory = 1 to UBound (vCategoires)
Set rCategory = rPatterns.Find(What:=vCategories)iCategory))
.Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex
Next
End With
End With
Next
End Sub
The above has been working fine, however on a specific workbook (one which contains a data table with +/- 100,000 records, a pivot table to analyze these records and subsequent worksheets with some 25 or 30 partial datasets against which I am plotting some +30 categorical charts – pies / bar charts), I am constantly getting the following error:
”
Run-time error ‘-2147467259 (80004005)’:
Method ‘ColorIndex’ of object ‘Interior’ failed
”
When debugging, the problem line in the code is apparently:
…
.Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex
…
When hovering the mouse pointer over the above rCategory, the result shown is
“rCategory.Interior.ColorIndex=-5″
To work around this problem, I have had to copy all the data sets, charts and category color table to a separate workbook, redefine my charts, reload the macro and run it, to get my chart categories colored correctly.
Can you please advise what the problem could be and how to go about fixing this?
Many thanks for your support.
ColorIndex is an old fashioned way to do colors in Excel 2003 and earlier. You could probably get better results using Color in Excel 2007.
I wonder though if the problem is because the charts were created in Excel 2003 and you’re trying to recolor them in 2007.
Hi Jon,
Again many thanks for your interest and continued support. Re my original post dated Feb 19 2010, I have all along been using Excel 2007;
Given you think using “ColorIndex” could be outdated in terms of Excel 2007, could you enlighten me as to the change to “Color” is simply to replace one for the other in my code? Or is there anything else you may consider appropriate?
Many thanks again. Kind Regards
Daniel -
If you use ColorIndex in 2007, it reverts to the default color palette from 2003. I think it ignores any customized colors stored in the Excel 2003 workbook. So I still use ColorIndex if all i need is a dull gray or a vivid pink.
If you use Color in Excel 2003 (which is based on RGB values), Excel picks the color in the palette it thinks is closest to the color you’ve specified. It’s generally not the color you think is closest, even if the RGB value you’ve specified is the same as that of the color you want.
Hi Jon…
You lost me in parragraph 2… I am using Excel 2007.
I have some 30 – 50 different categories, so what I have donde is build a 20 color-scheme which I re-cycle as of category 21, 41, and so on.
If I understand you correctly, I should stick with using ColorIndex to get the color closest to the rgb value I want….
Many thanks…
Excel 2007 doesn’t reliably to honor custom palettes developed in Excel 2003, and it doesn’t at all if the workbook has been saved in a 2007 format. So if you’re just working in 2007 with 2007 workbooks, use RGB.
I was just reminded of how wonderful your blog is… So helpful, and wanted to say thanks again for putting such useful information out here.
Hello, Thanks for posting this information. I was able to run the macro but I noticed that the colors in the chart are slightly off from the background colors of the cells that they are supposed to be referring to. In particular, a red color reads as purple. See the screenshot here:
http://postimage.org/image/1gwq37zr8/
Any ideas? Thank you in advance.
Hi TG -
This routine was written with Excel 2003 in mind. Excel 2003 had limited definition of colors: each allowable color had to fit among the 56 colors in the workbook’s color palette.
Excel 2007 and 2010 have the ability to specify any color, without the limitations of the color palette. When these versions see reference to ColorIndex, they make approximations based on the default Excel 2003 color palette. In this case, the colors you set in the worksheet don’t fit exactly to the color palette, so the resulting colors in the chart aren’t exactly as intended.
I believe is you change .ColorIndex to .Color in the code above, you should get matching colors in the chart and in the worksheet.
Sorry for the late reply. This worked, thank you!
Hey Jon,
Quick question for ya: I’m working on trying to create an automated ‘thermometer’ chart from a set of data. I would like the color in the chart (Red/Yellow/Green) to automatically display based on the percentage in one particular cell so I don’t have to change the color manually. F
Is this possible without using VBA? If so, how?
Thanks so much I appreciate it!!
-Rick
Rick -
Sure, this is possible using a VBA technique like this or VBA Conditional Formatting of Charts by Value. Or you could use worksheet formulas, as in Conditional Charts.
Hi Jon,
thank you for posting this code up, I have tried to apply it to my chart however like others it keeps coming up with an error on line
“.Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex” and error message “object variable and with block variable not set”
I am looking to colour pie charts and stack bar charts with specific colours. this is the code i have used so far:
Sub colourchart ()
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rCategory As Range
Set rPatterns = ActiveWorkbook.Worksheets(“Chart Colour Coding”).Range(“E5:E18″)
For Each chtob In ActiveSheet.ChartObjects
With chtob.Chart.SeriesCollection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rCategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex
Next
End With
Next
End Sub
—
any help is greatly appreciated!
Also, one last thing, is it possible to not display empty cells within the data range for a chart? It doesnt display on the actual chart, but you can still see on the legend / data labels…
thanks
Vivienne -
When it fails, and the .Points(iCategory)… line is highlighted yellow, open the Immediate Window, type the following, and press Enter:
?rCategory Is Nothing
If it returns True, then your previous statement did not find the matching label in the color-coded range.
Hi Jon,
Thanks for your reply.
It doesn’t come up with the error message anymore. However when I click a button which i created for the macro, it doesn’t colour the chart according to my specified colour scheme? Could you advise please?
I am using office 2010 incase you may need to know…
Thanks again
Will this code work with scatterplots? They can be ‘filled’ with an interior color…but i am running into issues with the .Points(iCategory) line. I don’t think rCategory is picking up the text. Oh – and .XValues doesn’t point to the labels.
Thoughts?
Ben -
In general this approach will work for any chart type. You need to use the appropriate formatting syntax for the chart elements. Make sure the series uses the right range for its X values.
What’s the text? If you need to handle this by numeric ranges, then simple text may not work. It may be better to try adapting this approach to your project:
http://peltiertech.com/Excel/Charts/ConditionalChart1.html
Hello Sir, I am working with line charts and facing one problem.
How to put category labels on x-axis. I have written a code which only puts numeric numbers on x-axis….can you please help me to display category labels.
here is my code:
Sub LineCharts()
Dim Ws As Worksheet
Dim NewWs As Worksheet
Dim cht As Chart
Dim LastRow As Long
Dim CurrRow As Long
Set Ws = ThisWorkbook.Worksheets(“Sheet1″)
LastRow = Ws.Range(“A65536″).End(xlUp).Row
For CurrRow = 2 To LastRow
Set NewWs = ThisWorkbook.Worksheets.Add
NewWs.Name = Ws.Range(“A” & CurrRow).Value
Set cht = ThisWorkbook.Charts.Add
With cht
.ChartType = xlLine
.SeriesCollection.NewSeries
.SeriesCollection(1).Values = “=” & Ws.Name & “!R” & CurrRow & “C3:R” & CurrRow & “C8″
.SeriesCollection(1).Name = “=” & Ws.Name & “!R” & CurrRow & “C2″
.Location Where:=xlLocationAsObject, Name:=NewWs.Name
End With
Next CurrRow
End Sub
KKL -
You need a similar statement that adds category labels to the chart, that is, that adds X values to the series. Something like this:
.SeriesCollection(1).XValues = “=” & Ws.Name & “!R” & FirstRow & “C3:R” & FirstRow & “C8″
Thank you so much Sir for your answer. I asked you, how to add category labels on the x-axis and your solution is working fantastically.
I need your help in one more problem.
In my last problem, I was plotting line chart on new worksheet each time. Each row had its corresponding line chart which was drawn on new worksheet. For example, if there were 10 rows, then 10 charts were plotted on each new worksheet.
Now I am trying to plot those charts on the same worksheet on which data(rows) are written. For Example, If there are 50 rows on sheet1, then after applying macro, 50 line charts should be drawn on the same worksheet (sheet1) but one after another sequentially. May be up and down in sequence.
I am Waiting for your kind reply,
Regards.
One more thing, The width of each chart should be 4 or 5 times wider than the default chart because i have 374 categories on the x-axis.
Regards,
KKJ -
This is starting to sound like the statement of work for a contracted project, and it’s also drifting pretty far from the topic of the article.
I suggest you break your project into smaller pieces and use my suggestions for Getting Answers For Your Excel Questions.
Sir, I am not working on a contracted project….I am a student….I am doing some research work and want to analyse my data through chart.
I only asked you, how to draw multiple charts in one worksheet? Because in my case, for each row, one chart is drawn on new sheet.
KKJ -
I said “sounds like” a contracted project, and honestly it does. And it’s off-topic. I like to answer questions that relate to a post, or that are reasonable to answer, but I’m as time-limited as everyone else.
You need to figure out why your code puts each chart on its own sheet, and fix the code. Is each chart on its own worksheet (which has rows and columns) or on its own chart sheet (which is just the chart)?
Read my “Getting Answers” post, and get some answers.
Jon,
I’m running into a problem with the macro. It definitely colors the cells based on a range, however it doesn’t use the exact colors that I’ve specified. For example, the range I’m using is AU19:AU26, and the cell in AU23 is labeled “Q4″ with RGB values of “55,96,146.” When I run the macro, it colors the “Q4″ items in the graph with RGB values of “55,55,255.”
Any ideas on how to fix it?
Thanks,
Scott
How do i grab the Category name of a pie chart in vba? i want to take that value and use it in another part of the workbook when someone clicks on a certain pie slice.
Derek -
Check out my article on Chart Events in Microsoft Excel.
Hi Jon,
first of all thank you a lot for this very nice code:
Sub ColorByCategoryLabel()
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rCategory As Range
Set rPatterns = ActiveSheet.Range(“A1:A4″)
With ActiveChart.SeriesCollection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rCategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex
Next
End With
End Sub
The problem for me is, that the diagram has to have specific colours (using the rgb color model for example). When I dye the cell A1 in this colour the diagramm gets a slightly different color.
Do you have a solution for this problem?
I would be very thankful for your answer!
Best wishes,
Tobias
Tobias -
This code was written for the Excel 2003 color system. Try using .Color in place of .ColorIndex.
You’re a genius!!
Thanks a lot, it works perfectly :-)
Hi Jon,
Firstly thank you for the excellent and intersting posts on this subject. I have what i fear is a very mundane question but am drawing a blank on it.
I would like to conditionally format certain values on a graph dependent on whether i have marked the row or cell in my chart.
ie. I have a sheet with values on it for certain categories subscribed to different regions in a country, these change by column with regions in the rows. I would like to study certain regions ans highlight these in the chart by using a different colour to the other bars. Traditionally I have manually selected each one and recoloured buyt feel there must be a smarter way to do this. Have a missed a post on your site that covers this off?
Any help gratefully appreciated
Regards
Dominic
Dom -
You could apply the technique described in Conditional Formatting of Excel Charts. Mark a data point with an entry in another column, then make your formulas indicate marked points in a new series.
How to do, if labels Range (A1:A4) are given by formulas not values? In this case macros for the 1st label will take the second label color and macros ends with error message (Run-time error 91 – object variable or With block variable not set).
Thanks
N.
It shouldn’t matter if the range has constants or formulas.
What line is highlighted when you receive the error? Did you activate your chart before running the code?
It stops at this line:
.Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex
When category labels are as values not links or formulas, then everything is fine
Probably the range rCategory is undefined. This likely means there was no match. Make sure the formulas are constructing labels that exactly match the labels in the chart.
Hi Jon,
Your code for VBA Conditional Formatting of Charts by Category Label worked great. I am now trying to do something similar but for a plot chart that has names in the series section that I want to be a specific color. I can’t figure out how to modify the code from conditional formating by category to conditional formating or color coding by the series name.
Joan -
Formatting by series name was done in VBA Conditional Formatting of Charts by Series Name. It uses the Excel 2003 ColorIndex code for the fill colors, though, not the newer 2007 syntax. Just one more article in my list of things to update.
Jon,
Thanks for this link. Two questions: 1) Is there a way to modify the code so that it will use the same color for EnronA and EnronB? 2) Some of the colors I pick show up differently in the chart. For example, I pick a light brow for my target color and in the chart it show up a light beige. I am using Excel 2010. Both issues have come up when color coding by category and by series.
Jon -
To get the same color for two different series, put both series names into cells within the formatted range of the worksheet, and color both cells the same.
Excel 2007 introduced a different color system, so the old ColorIndex approach doesn’t always work very well. I’ve updated this post so it now has two code samples.
Thanks for the new code for Excel 2007. It worked great. My issue with the labels is that for every report I might have a different modifier, like Enron/A, then, Enron/C, Enron/F. I may not know the modifier ahead of time. I didn’t know if there is a way for the program to just look at the first few characters, like Enr with some wildcard symbol and so the color for all Enrons will be the same. I was trying to avoid doing it manually as you suggested. Alternatively, I remove the modifiers, change the colors and then put the modifiers back, but this is still manual.
We should probably be doing this in the article re: Series Names, but anyway.
You need to decide how much you need the series name to match the placeholder label. If it’s always “Enron” plus a designator, then something like this might be fine:
One of the cells in the color-code range will contain “Enron”. Using Find won’t be enough, instead we’ll loop through the range until we get a partial match for the series name, then apply the formatting. If there are multiple partial matches, this will select the first. I converted the strings to lower case to make the comparison case insensitive.
And I kept the categories in this, not series names, but you’ve got that figured out already.
Hi Jon,
for the last entry, there is a test thus: If Left$(sCategory, Len(sCell)) = sCell…
However, if the value of sCell is subsequently changed then the color needs be reset. e.g.
If Left$(sCategory, Len(sCell)) = sCell then
‘set color (as above)
else
‘reset color
end if
Please can you detail how to reset the color per the original graph series color?
thank you…
I recorded a macro in Excel 2010 in which I changed the color of a series, then changed it back to automatic. The step that changed the color back to automatic was missed by the recorder. And the Object Browser didn’t have anything that looked helpful. But I tried the following Excel 2003 command, and it seems to set the fill color back to the default.
Hi Jon
Your posts on charting are brilliant!! I wondered if there is any way you can apply conditional formatting to chart by data label?
I have a stacked bar chart showing a schedule of events in different venues – the venues being the category labels in the Y axis, time being the X Axis (the series are based on the duration of events and breaks between events – with the break series’ having no fill).
I’m looking to conditionally format the fill colour of the data point based on the data label of that point (the data labels are linked to other cells in the worksheet, rather than the chart data itself).
Any help gratefully received!!!
Thanks
Layla
Layla -
The quick answer is Yes. You need to read the data label, via SeriesCollection(i).Points(j).DataLabel.Text, and compare it to your criteria, then apply the appropriate format.