I have several tutorials on this blog 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 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.
Related Articles about VBA Conditional Formatting of Charts
- VBA Conditional Formatting of Charts by Series Name
- VBA Conditional Formatting of Charts by Value
- VBA Conditional Formatting of Charts by Value and Label
- Conditional Formatting of Lines in an Excel Line Chart Using VBA
Pablo says
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?
Jon Peltier says
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
Arnaud says
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.
Jon Peltier says
Set rPatterns = ActiveSheet.Range(“A1:A4”)
Have you selected a chart? The procedure needs an active chart.
J says
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.
Jon Peltier says
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 SubLEM says
This 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!!
LEM says
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…
Jon Peltier says
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
LEM says
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 SubJon Peltier says
Hi LEM –
Thanks for posting this. I’ve made one minor change to prevent seizures in those with a low threshold.
ckz says
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 SubJon Peltier says
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.
ckz says
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
ckz says
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
Jon Peltier says
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.
ckz says
perfect. thanks for the quick response.
ckz
LEM says
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?
LEM says
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!!
Jon Peltier says
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.
Vanessa says
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!
Jon Peltier says
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 “”.
Vanessa says
Yes, it’s a bar chart. I tried this, but instead of #N/A it now shows zeroes!
Jon Peltier says
What do the formulas look like?
nong says
Jon Peltier
i want to put series color(different solid color to fill 4 series) could you show me the VBA code please
Jon Peltier says
Nong –
Do you want this: VBA Conditional Formatting of Charts by Series Name?
Danièle says
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
Jon Peltier says
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.
Danièle says
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
Jon Peltier says
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.
Danièle says
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
Samreen says
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!
Jon Peltier says
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.
Steven says
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.
Jon Peltier says
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)”.
Steven says
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.
Jon Peltier says
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
Steven says
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!
Jorge says
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
Jon Peltier says
Jorge –
In the page you’ve cited, the chart has to be selected. Note the line:
With ActiveChart.SeriesCollection(1)
Jorge says
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
Jon Peltier says
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.
tyler says
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
Jon Peltier says
Tyler –
Make sure vCategories(iCategory) (the ith X axis label) actually exists in the rPatterns range.
Paul PW says
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
Jon Peltier says
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).
Felipe says
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
Felipe says
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
Jon Peltier says
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.
Sandy says
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.
Jon Peltier says
Sandy – What line of code is highlighted? That should give a strong hint about which object or variable is not defined.
Daniel Murray says
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
Daniel Murray says
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
Jon Peltier says
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.
Daniel Murray says
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
Daniel Murray says
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.
Jon Peltier says
Daniel –
Try something like this:
Daniel Murray says
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.
Jon Peltier says
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.
Daniel Murray says
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
Jon Peltier says
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.
Daniel Murray says
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…
Jon Peltier says
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.
LEM says
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.
TG says
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.
Jon Peltier says
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.
TG says
Sorry for the late reply. This worked, thank you!
Kinric says
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
Jon Peltier says
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.
Vivienne says
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
Jon Peltier says
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.
Vivienne says
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
ben says
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?
Jon Peltier says
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:
https://peltiertech.com/conditional-formatting-of-excel-charts/
kkj says
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
Jon Peltier says
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″
kkj says
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.
kkj says
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,
Jon Peltier says
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.
kkj says
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.
Jon Peltier says
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.
Scott says
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
Derek says
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.
Jon Peltier says
Derek –
Check out my article on Chart Events in Microsoft Excel.
Tobias says
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
Jon Peltier says
Tobias –
This code was written for the Excel 2003 color system. Try using .Color in place of .ColorIndex.
Tobias says
You’re a genius!!
Thanks a lot, it works perfectly :-)
dom says
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
Jon Peltier says
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.
Nauris says
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.
Jon Peltier says
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?
Nauris says
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
Jon Peltier says
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.
jon says
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.
Jon Peltier says
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 says
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 Peltier says
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.
jon says
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.
Jon Peltier says
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.
JohnS says
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…
Jon Peltier says
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.
Layla says
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
Jon Peltier says
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.
Doris says
Thanks, Jon – this is why yours is my absolutely favourite Excel site!!!
I was able to adapt this to do exactly what I needed, and in a couple of minutes save myself and my colleagues hours of repeated manual reformatting :-)
Doris
Doris says
A suggestion:
In the innermost loop, it’s more robust to supply an additional parameter to the Find function, so you don’t end up accidentally finding parts of words – speaking from recently acquired experience :-)
Set rCategory = rPatterns.Find(What:=vCategories(iCategory), LookAt:=xlWhole)
Jon Peltier says
Doris –
Good point. My labels were completely unrelated, but if one label is contained with another, VBA may well apply the wrong formats.
Lee says
Hi Jon,
Great code. Works brilliantly in a column chart I am using but in the same sheet there is a stacked column chart and this code isn’t working for that. I’m fairly new to VBA so I’m sure it is my misunderstanding that is causing this issue but I’d be very grateful of your help.
I only need to re-colour one series. So each category needs to be coloured as per its related category in the data range but only one series within that category needs to be selected! It is series 2 that I need to update.
Do you know how I can acheive this. I have tried selecting the series by adding a line into your code but I have been unsuccessul so far.
Thanks in advancve for any help Jon
Lee.
Lee says
Hi Again,
Just my last post and saw the need for me to claify the issue further!!!
I have selected series 2 using the below code but it is every series other than series 2 that is re-coloured!!!!
Thanks again for your help
Lee.
Jon Peltier says
I think this is what you want:
Lee says
Hi Jon,
Thanks for your help on this.
This code fails at the below point.
.Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex
do you know what could be causing this?
Thanks again
Lee
Jon Peltier says
Lee –
What is the error description (not just error number)? Has rCategory been properly defined?
Lee says
Hi Jon,
Thanks for your response
The error description is ‘Object variable or with block variable not set’
I’m not sure what is causing this. Where might I being failing in terms of defining rCategory.
Thanks again
Lee.
Jon Peltier says
When the code has failed and the line is highlighted, open the Immediate Window (View menu of the VB editor). Type the following line and press Enter:
?rCategory.Address
Does this return the expected address? If so, enter this line and press Enter:
?rCategory.Interior.ColorIndex
What do you see?
It should be noted that the .ColorIndex code is legacy Excel 2003 code. When I get a minute or three I need to rewrite this series of posts using Excel 2007 code.
Lee says
Hi Jon,
When I type ?rCategory.Address
in the immediate window and press enter I get a pop up that says ‘Runtime error 91 – Object variable or with block variable not set’
The code correctly colours the first category correctly but fails when it reaches the second. I’ve used your code on a column chart in the same project and it works perfectly but the stacked column is causing the problems
Again I really appreciate your help on this. The project I am working on will be well worth all of the hassle when its complete so your help is very much appreciated
Thanks
Lee.
Jon Peltier says
“Object variable or with block variable not set”
This means that
rCategoryisn’t defined. It must mean thatSet rCategory = rPatterns.Find(What:=vCategories(iCategory))didn’t find a cell in
rPatternsthat matched the entry invCategories(iCategory)Lee says
Fantastic.
That’s it. The code wasn’t recognising the XValues because one of the category labels was a formula. I used a formula so that a month name would update each month. I have changed this to just appear as “Current Forecast Accuracy” rather than using the month name and it now works.
Thanks so much for your help on this Jon
Cheers
Lee.
Prateek kothari says
Hey Jon,
I was just going through your vba and conditional formatting blogs and i am currently facing a problem with that.
Actually i use a tool that builds up the powerpoint presentations from the excel .
workflow is
EXCEL – TEMPLATE- POWERPOINT
We were all fine when were doing without any conditional formatting but when we try to incorporate conditional formatting it reads out ” SPECIFIED OBJECT OUT OF RANGE”. So we thought that it might be coming as the integer range is not defined as long. We changed all of them but it was not as useful. It stops at random places. So we try to increase the timing of the code as it is taking to copy the data . but then it randomly skips something and pasting .
I would like to show u my piece of code.
TARGET is used when we are copying the data without conditional formatting
AA TARGET is used when we are copying the data with conditional formatting.
For Each pptSlide In pptTargetPresentation.Slides
WriteLog “|Slide No.: ” & pptSlide.SlideIndex
OWWA.RowFont True, False, 11, 5
Set TargetTables = New Collection
Set TargetCharts = New Collection
Set TargetGraphs = New Collection
Set TargetNewcharts = New Collection
Set TargetRenewTables = New Collection
pptApp.ActiveWindow.View.GotoSlide pptSlide.SlideIndex
For Each shpTarget In pptSlide.Shapes
shpTarget.Select
If shpTarget.HasTable Then
ReplaceTokens shpTarget
TargetRenewTables.Add shpTarget ‘Checks if it is a table
Else
If HasChart(shpTarget) Then ‘ Checks if it is a Powerpoint 2007 chart
TargetNewcharts.Add shpTarget
Else
If shpTarget.Type = msoEmbeddedOLEObject Then
If shpTarget.OLEFormat.progID = “MSGraph.Chart.8” Then
TargetCharts.Add shpTarget
End If
Else
If Left(UCase(Trim(shpTarget.AlternativeText)), 6) = “TARGET” Then
If shpTarget.HasTextFrame Then
If shpTarget.AutoShapeType = msoShapeRectangle Then
TargetTables.Add shpTarget
ElseIf shpTarget.AutoShapeType = msoShapeRoundedRectangle Then
TargetGraphs.Add shpTarget
End If
End If
Else
ReplaceTokens shpTarget
End If
End If
End If
End If
Next
‘Process tables
For Each shpTarget In TargetTables
oParse.ParseString = shpTarget.TextFrame.TextRange.TrimText.Text
SourceRef = oParse.Token(1)
TargetType = RTrim(oParse.Token(2))
Set rngSource = GetRange(SourceRef)
If IsValid(rngSource) Then
Select Case UCase(TargetType)
Case “EXCEL”
Set shpFromExcel = PasteExcelRangeToPPT(rngSource, pptSlide)
SetPosition shpFromExcel, shpTarget
WriteLog “||Pasted as EXCEL data from Range {” & SourceRef & “}”
OWWA.RowFont False, False, 10, 5
Case “PICTURE”
rngSource.Copy
Set shpFromExcel = pptSlide.Shapes.PasteSpecial(ppPasteEnhancedMetafile)
SetPosition shpFromExcel, shpTarget
WriteLog “||Pasted as PICTURE data from Range {” & SourceRef & “}”
OWWA.RowFont False, False, 10, 5
Case “TABLE”
rngSource.Copy
Set shpFromExcel = pptSlide.Shapes.PasteSpecial(ppPasteDefault)
shpFromExcel.Select
pptApp.ActiveWindow.Selection.ShapeRange.IncrementLeft shpTarget.Left – shpFromExcel(1).Left
pptApp.ActiveWindow.Selection.ShapeRange.IncrementTop shpTarget.Top – shpFromExcel(1).Top
shpTarget.Delete
WriteLog “||Pasted as TABLE data from Range {” & SourceRef & “}”
OWWA.RowFont False, False, 10, 5
Case Else
End Select
TableCount = TableCount + 1
End If
Next
‘Pasting Charts as Pictures
For Each shpTarget In TargetGraphs
oParse.ParseString = shpTarget.TextFrame.TextRange.TrimText.Text
SourceRef = oParse.Token(1)
TargetType = RTrim(oParse.Token(2))
Set excelGraph = GetChartObject(SourceRef)
If IsValid(excelGraph) Then
excelGraph.CopyPicture
Set shpFromExcel = pptSlide.Shapes.PasteSpecial(ppPasteEnhancedMetafile)
SetPosition shpFromExcel, shpTarget
WriteLog “||Pasted as PICTURE graph from {” & SourceRef & “}”
OWWA.RowFont False, False, 10, 5
ExcelChartCount = ExcelChartCount + 1
End If
Next
‘Updating MS Graph Charts
For Each shpTarget In TargetCharts
‘ Fix for Powerpoint 2007, otherwise alternative text shows blank
shpTarget.Copy
Dim shpTemp As Object
Set shpTemp = pptSlide.Shapes.PasteSpecial(ppPasteDefault)
shpTarget.AlternativeText = shpTemp.AlternativeText
shpTemp.Delete
oParse.ParseString = shpTarget.AlternativeText
‘—————————————————————
If Left(UCase(Trim(shpTarget.AlternativeText)), 6) = “TARGET” Then
SourceRef = Replace(Replace(RTrim(oParse.Token(2)), Chr(10), “”), Chr(13), “”)
Set rngSource = GetRange(SourceRef)
If IsValid(rngSource) Then
rngSource.Copy
Set pptChart = shpTarget.OLEFormat.Object
pptChart.Application.Datasheet.Range(“00”).Paste ‘True
pptChart.Application.Update
pptChart.Application.Quit
WriteLog “||Updated PPT Chart with data from {” & SourceRef & “}”
OWWA.RowFont False, False, 10, 5
PPTChartCount = PPTChartCount + 1
End If
End If
Next
‘ Process Powerpoint 2007 charts
For Each shpTarget In TargetNewcharts
shpTarget.Chart.ChartData.Activate
Set wksDest = shpTarget.Chart.ChartData.Workbook.Worksheets(1)
Dim r As Integer
Dim c As Integer
Dim Rng As Range
Dim src As String
If Not IsEmpty(wksDest.Range(“A1”).Value) Then
If Left(UCase(Split(wksDest.Range(“A1”).Value, “|”)(0)), 9) = “AA_TARGET” Or Left(UCase(Split(wksDest.Range(“A1”).Value, “|”)(0)), 6) = “TARGET” Then
SourceRef = Replace(Replace(RTrim(Split(wksDest.Range(“A1”).Value, “|”)(1)), Chr(10), “”), Chr(13), “”)
wbSource.Worksheets(“Control”).Range(“Replace003”).Value = SourceRef
Calculate
SourceRef = Mid((wksDest.Range(“A1”).Value), 11, 23)
If Left(UCase(Split(wksDest.Range(“A1”).Value, “|”)(0)), 9) = “AA_TARGET” Then
SourceRef = Right(Split(wksDest.Range(“A1”).Value, “|”)(0), Len(Split(wksDest.Range(“A1”).Value, “|”)(0)) – 10)
ElseIf Left(UCase(Split(wksDest.Range(“A1”).Value, “|”)(0)), 6) = “TARGET” Then
SourceRef = Right(Split(wksDest.Range(“A1”).Value, “|”)(0), Len(Split(wksDest.Range(“A1”).Value, “|”)(0)) – 7)
End If
Set rngSource = GetRange(SourceRef)
If IsValid(rngSource) Then
‘wksDest.Cells.Clear
rngSource.Copy
wksDest.Range(“A1”).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
wksDest.Range(“A1”).PasteSpecial Paste:=xlPasteColumnWidths
r = Selection.Rows.Count ‘Counting rows and columns of pasted data
c = Selection.Columns.Count
Set Rng = Range(Cells(1, 1), Cells(r, c))
src = “=’Sheet1’!” & Rng.Address
shpTarget.Chart.ChartData.Workbook.Activate
‘shpTarget.Chart.SetSourceData Source:=src ‘Changing the data source according to the new data
WriteLog “||Updated Excel 2007 Chart with data from {” & SourceRef & “}”
OWWA.RowFont False, False, 10, 5
NewChartCount = NewChartCount + 1
End If
ElseIf UCase(Split(wksDest.Range(“A1”).Value, “|”)(0)) = “TARGET” Then
SourceRef = Replace(Replace(RTrim(Split(wksDest.Range(“A1”).Value, “|”)(1)), Chr(10), “”), Chr(13), “”)
Set rngSource = GetRange(SourceRef)
If IsValid(rngSource) Then
‘wksDest.Cells.Clear
rngSource.Copy
wksDest.Range(“A1”).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
wksDest.Range(“A1”).PasteSpecial Paste:=xlPasteColumnWidths
r = Selection.Rows.Count ‘Counting rows and columns of pasted data
c = Selection.Columns.Count
Set Rng = Range(Cells(1, 1), Cells(r, c))
src = “=’Sheet1’!” & Rng.Address
shpTarget.Chart.ChartData.Workbook.Activate
‘shpTarget.Chart.SetSourceData Source:=src ‘Changing the data source according to the new data
WriteLog “||Updated Excel 2007 Chart with data from {” & SourceRef & “}”
OWWA.RowFont False, False, 10, 5
NewChartCount = NewChartCount + 1
End If
End If
End If
shpTarget.Chart.ChartData.Workbook.Close
pptTargetPresentation.Application.Activate
Next
‘ Update existing Tables
For Each shpTarget In TargetRenewTables
Dim sCheck As String
Dim TargetTable As Table
Set TargetTable = shpTarget.Table
sCheck = TargetTable.cell(1, 1).Shape.TextFrame.TextRange.Text
If sCheck “” Then
‘Updated by AA
‘It will take the value after | symbol and paste it in the Rplace003 name range. The name range present after AA_Target will be then used as the name range to paste values in the powerpoint table
If Left(UCase(Split(sCheck, “|”)(0)), 6) = “TARGET” Or Left(UCase(Split(sCheck, “|”)(0)), 9) = “AA_TARGET” Then ‘Update code to paste the format as well
SourceRef = Replace(Replace(RTrim(Split(sCheck, “|”)(1)), Chr(10), “”), Chr(13), “”)
If UCase(Split(sCheck, “|”)(0)) “TARGET” Then
wbSource.Worksheets(“Control”).Range(“Replace003”).Value = SourceRef
Calculate
If UCase(Split(sCheck, “|”)(0)) “TARGET” And Left(UCase(Split(sCheck, “|”)(0)), 9) = “AA_TARGET” Then
SourceRef = Right(Split(sCheck, “|”)(0), Len(Split(sCheck, “|”)(0)) – 10)
ElseIf UCase(Split(sCheck, “|”)(0)) “TARGET” And Left(UCase(Split(sCheck, “|”)(0)), 6) = “TARGET” Then
SourceRef = Right(Split(sCheck, “|”)(0), Len(Split(sCheck, “|”)(0)) – 7)
End If
End If
Set rngSource = GetRange(SourceRef)
If IsValid(rngSource) Then
Dim rtcount As Integer, ctcount As Integer, rt As Integer, ct As Integer
rtcount = TargetTable.Rows.Count
ctcount = TargetTable.Columns.Count
Dim rscount As Integer, cscount As Integer
rscount = rngSource.Rows.Count
cscount = rngSource.Columns.Count
If rscount rscount)
TargetTable.Rows(rscount + 1).Delete
Loop
Else
Do While (TargetTable.Rows.Count < rscount)
TargetTable.Rows.Add
Loop
End If
If cscount cscount)
TargetTable.Columns(cscount + 1).Delete
Loop
Else
Do While (TargetTable.Columns.Count < cscount)
TargetTable.Columns.Add
Loop
End If
rt = 1
ct = 1
'Code Updated by Avneesh Abrol
'Code updated to paste special formatting in the ppt table. Here, if we have AA_Target instead of Target, it pastes the formatting. Else it just pastes the text.
If Left(UCase(Split(sCheck, "|")(0)), 9) = "AA_TARGET" Then
For rt = 1 To rscount
For ct = 1 To cscount
rngSource.Cells(rscount + 1 – rt, cscount + 1 – ct).Copy
TargetTable.cell(rscount + 1 – rt, cscount + 1 – ct).Shape.TextFrame.TextRange.Paste
' For TPVar1 = 1 To 100000
' For TPVar2 = 1 To 10000
' Next TPVar2
' Next TPVar1
Next ct
Next rt
Else
For rt = 1 To rscount
For ct = 1 To cscount
TargetTable.cell(rscount + 1 – rt, cscount + 1 – ct).Shape.TextFrame.TextRange.Text = rngSource.Cells(rscount + 1 – rt, cscount + 1 – ct).Text
Next ct
Next rt
End If
RenewTableCount = RenewTableCount + 1
WriteLog "||Updated Table with data from {" & SourceRef & "}"
OWWA.RowFont False, False, 10, 5
End If
End If
End If
Next
Next
On Error Resume Next
name_team = GetRange(PPTTeam)
total_team = GetRange(TotalRange)
If wkbIsOpen = False Then wbSource.Close
End If
Can u please assist us in it ??
It would be of great help.
Jon Peltier says
Prateek –
I’m sorry, I think it would take me half a day to get my head around that code.
Prateek kothari says
I know its quite tedious. Can u assist me a code which can help me to bring conditional formattoing ( coloured values, coloured names and small other formatting which is coming from the workbook./
Excel – Template- No of Presentations.
It would be of great help.
Prateek kothari says
I know it is tedious but can u please assist me in keeping the conditional formatting of the workbook in the preentation. I mean if u have any piece of code that can help me out in this. Also can u please tell me more about this kind of error ” SPECIFIED VALUE IS OUT OF RANGE”, Also can u please assist me in how to have a good command on vba as i m just a beginner…
it would be of great help.
Jon Peltier says
Prateek –
It’s a long process to capture the formats produced by conditional formatting, because the cells don’t know what it is. First you have to evaluate the conditions, for example, if it’s a formula, determine if the formula is true or not. If it’s true, then you have to determine from the formatting rule what the format is, then apply the format.
Is it possible to simply copy the range as a picture, and paste that into PowerPoint?
Prateek kothari says
Jon but the presentation that i made include charts and tables on the same slide. The problem is if i am pasting it as a picture then it goes into the middle of the slide. So it will gives us no flexibility at all. BTW what this error meant “Specified value is out of range” ?
Jon Peltier says
You can resize and reposition the pasted picture of your Excel content. Flexibility regained.
The error message needs context. What line was highlighted when the error message appeared?
Prateek kothari says
Can i make the resizing and reposition be automated. I mean there is a template of 152 slides. I use it as a base and with the excelerator ( Code above) we generate various presentations. So if the pasting can be incorporated automatically, then we dont have any problem. So can u please assist me how to automate the positon.
Jon Peltier says
This ancient tutorial shows how to ask the user for a scaling percentage, and then resize the pasted shape:
https://peltiertech.com/Excel/XL_PPT.html#charts1slide
With a teeny bit of algebra you can make any arbitrarily sized pasted shape fit into a predefined size region on the slide.
Prateek kothari says
Jon-
Thanks for the knowledge. Actually, the presentations that we made are re QCed by our main team . So if we use picture then they are not able to go to the back end or do any kind of formatting. So we cant have them as pictures.
By the way hats off to your Dedication , knowledge and helping nature. Your blog is awesome.
Jamie says
Hi Jon,
Firstly thanks for this great tutorial! I’m trying to do something really similar but format the chart based on user defined pictures to fill each bar rather than colours. I have a chart of countries with each bar represented by a flag of the country which is specified from a picture of the flag I have stored on my hard drive. I just manually go through each data point and assign a .gif file with the country’s flag to the chart. Problem is when data is changed and more countries added, the flags move to different countries! I’ve tried looking for help on here (example chart posted):
http://www.ozgrid.com/forum/showthread.php?t=183393
but so far no joy until I found your great site and I’m nearly there (I think). Do you know of a way I can either get each bar in the chart to point to the file with the same name as the data series (.gif) to fill or have a separate sheet with the file paths of each possible flag which can fill it automatically? The above code would work if instead of flat colours in the cells, if I could fill the entire cell with a flag picture but it treats pictures differently and I’d think code would be different to fill by picture rather than colour based on category label. Any advice would be much appreciated.
Miguel says
Hi, Jon
Somehow this stopped working today with Excel 2013. The error message says:
Object variable or With block variable not set
And the line referred is:
.Points(iCategory).Format.Fill.ForeColor.RGB = rCategory.Interior.Color
Jon Peltier says
Miguel –
That error means Excel doesn’t know either (a) what the point is or what series the point belongs to (is iCategory larger than the number of points in the series?), or (b) what rCategory is (was a match not found in the previous statement?).
Ram says
Hi Jon,
Firstly, great original post. The code is terse and yet general enough for many situations. It works very well for a single X-category. What if I have multi-level X-axis categories? Can you help with this?
I’m still a novice at VBA but I’m guessing that ” vCategories = .XValues ” … this is where the category values are assigned. Could you help with how this assignment needs to change for multiple levels of X categories? My example is below… and I would like different color assignments for each fastner type only:
X axis Y axis
cat 1 cat 2 values
Part A Fastner 1 30
Fastner 2 20
Part B Fastner 1 10
Fastner 2 5
Thank you.
R
Ram says
Sorry the example did not type out correctly. Maybe this will help:
Column A contains the broader categories (Part A and Part B)
Column B Contains narrower Categories (Fastner-1 and Fastner 2)
Column C contains the values.
I was hoping to have Fastner-1 in the same color for Parts A and B.
Cells: content
A2: Part-A
A4: Part-B
B2: Fastner-1
B3: Fastner-2
B4: Fastner-1
B5: Fastner-2
C2: 30
C3: 20
C4: 10
C5: 5
Jon Peltier says
Generally it’s better to find a non-VBA way to handle formatting. A little data rearrangement can make it easy to format by series.
In a regular clustered column chart, you can format Fastener 1 and Fastener 2 differently:
Same in a clustered column chart with dual categories:
If you can’t do these, forget about trying to make sense of dual layer categories using .XValues. Instead, you can parse the series formula to find the range that contains the X values (A2:B5), then find the appropriate labels in the second column, then change the formatting of the corresponding point in the series.
Ram says
Thanks Jon. I finally ended up using your code. I got the values to output using some of your code from another discussion and used those values to assign colors and it worked.
Apparently with multilevel Xaxis excel assigns values as “Part A Fastener 1” … i.e. with a space between the two levels.
Thanks, anyway. Your posts are great.
Nathan says
Hi John,
Ive been using this code with success for several months, however I recently tried running the macro and I am now getting the “Object variable or With block variable not set” error. I havent changed anything in my workbooks and so Im not sure why it would just stop working, where before it was working perfectly.
Full code that im using…
———————————
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(“A1:A23”)
‘ 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 Sub
————————–
“.Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex” is the sentence that is being highlighted by the debugger. When i hover over “iCategory” is says “= 9”, when I hover over “rCategory.Interior.Colorindex” it says “= Object variable or With block variable not set”.
Any ideas as its driving me mad??
Thanks
Nathan says
Please ignore my previous comment… fresh pair of eyes after lunch. Someone had gone into the excel book and renamed one of the categories in the chart data (acronym version)… not realising it was being used in the colour lookup list on ‘sheet 1’
Stephan says
Is it possible to make vba so that the bar with specific name for a couple of charts is automatically always one color even when it moves position in the chart data?
Thanks
Reed Jordan says
Hey Jon – Just want to let you know I found this really helpful for visualizing a data presentation for a client. Still useful in 2014! Keep it up.
John says
Jon,
Great stuff here. I am trying to use this code to format pivot charts but I am having some problems. The code seems to work for some of the charts but not all. All of my pivot charts are on the same sheet as are their corresponding pivot table. All charts have one series with one or more categories. I have my colors set on a separate sheet (Colors) in the same workbook. There are 5 colors (A1:A5).
When the code runs there are no error messages. The only issue is that some charts are not formatted to the correct colors.
Here is the code:
Thanks for your feedback.
Jon Peltier says
John –
The .Interior.ColorIndex syntax is a carryover from Excel 2003, and is not always reliable. If you’re using Excel 2007 or later, use the .Format.Fill.ForeColor.RGB and .Interior.Color syntax from the last example of the article.
Is the chart type one that uses fills like this (column, bar, area)? Do the category labels in the chart exactly match the text in the colored range in sheet “Colors”?
John says
Jon,
Thanks for your fast response. The charts are all pie charts. The category labels do exactly match the text in the color range. I am using Excel 2010.
The new code you suggested is working better. One small problem. If the pie chart only has one category, the macro changes the pie to a blue color and ignores the color selection for the category in the “Colors” sheet.
Suggestions?
Thanks again for your support.
Jon Peltier says
John –
If the pie was constructed using only the label cell and the value cell, Excel may have used the label cell as the series name rather than as the category label. The series then has no categories, so
produces an array with a single empty item. Simply switching rows and columns was enough to fix the problem when I tried it.
Naren says
Hi Jon –
I’m trying to use this method but have multiple x values on my chart. What would I need to do in the code to make it work?
Thanks,
Naren
Naren says
To clarify, I have multiple x values; however, I only want to use one of them for my color requirements.
Jon Peltier says
Hi Naren –
Not sure what you mean. Could you post a sample of the data, and tell how you want it used?
jhosep says
hi jon continuous problems with my macro graphic I commented as this has 3 values, 2 are static and one varies daily, and what I want is that the value that varies change color when less is red and when it is higher in green could not upload the file but here you explain how is my data table
advisers
-petter
-brown
-Carlos
Bryan
annual target
– 100%
– 100%
– 100%
– 100%
daily goal
– 28%
– 28%
– 28%
– 28%
grain sold to date
-Here varial numbers and these are the changes from red if meno and green if it is greater than the daily goal
– 17%
– 45%
– 12%
– 5%
Tamir Basin says
Hi Jon,
I have a dynamic chart. It is originated from a pivot chart with some labels in the columns area. Not always all labels are showing as a result of a slicers pick.
The code you offered works like a charm when all(!) labels are showing only. but when 1 label is missing (has no data to show) then the colours are assigned differently and do not present the same name of the label.
Do I make sense?
Thank you,
Tamir
Claudiu Clescu says
Hi Jon,
Not sure if this post will still receive answers, but I’m giving it a try.
I used the below code (same as yours, just expanded the range to my needs) and it works flawlessly (thank you for that ;)). The one problem I have is that the label text colors are not the same any more with the slices. I need pie charts where the label text color remains the same as the slice of the pie and unfortunately, after running the code, the label text color is different… Is there any sequence I can add to the code, so that I get this fixed?
And lastly, as I am building my pie charts based on a slicer selection, I see I need to run the macro each time after changing the selection. I can live with that, no biggy, though I have 4 charts where I would need to manually run the macro each time when a new selection is made in the slicer, but I was wondering if there is any way I can automate this (macro to run automatically when changing selection and, if possible, for all 4 charts).
Code:
Jon Peltier says
Claudiu –
Last question first, use an object variable (chtob) to cycle through charts on the sheet:
First question, put this code into the code module behind the sheet with the pivot table:
Dylan says
Hi John,
I’m using the following code to color the bars according to a specified range of cells.
My data includes dates and funds. I want the series(i) to refer to the funds and not the dates which it currently is selecting.
Any help would be appreciated.
Thanks
Jon Peltier says
Dylan –
Your categories are dates and your series names are funds, right? So you should probably look at a companion tutorial, VBA Conditional Formatting of Charts by Series Name.
Isobel says
I can’t seem to get this to work on my doughnut chart, I keep getting the errors message “Subscript out of range”.
My chart categories are in C7 – Not started, D7 – Late, E7 – In progress, and F7 – Completed.
In each case the data is located in the appropriate column in rows 7 thru 18.
Each individual donut chart only references one row at a time eg cells C7:F7. There is an individual donut chart for each of the rows 7 thru 18, each one referencing the next row down.
There will be approximately 180 charts and I’d like them in a bit of a dashboard such that when I copy and paste an existing donut chart, it will keep the colour formatting.
Incidentally, is there any way that VBA can ‘make’ the donut chart keep its labels, label formatting and exploded slice formatting (that currently I’ve applied manually) because it’s all retained when I cut and paste a chart, but when I then re-reference that chart to a different row of data, all the chart label and slice formatting disappears.
If anyone can help I’d be so grateful!!
Jon Peltier says
Isobel –
Are the categories always the same in all of the charts? If so, then you don’t need to use code to color them. Create and format your first chart, then before you make copies, you need to change the “Properties Follow Chart Data Point” setting, as described in my Properties Follow Chart Data Point tutorial. Click on the
Filetab, chooseOptionsat the bottom of the list, click onAdvanced, scroll down toChart, and uncheck the box forProperties Follow Chart Data Point for Current Workbook. With this setting in place, you can now make copies and adjust the data, and the formats will not change when the data is moved.April M says
Hi John – this is an awesome resource. Thank you! I am using Excel 2016, the code is working for me, but I am having problems with the Legend. The series are filling in with the correct color, but the outside line of each color box is clear. So it displays as a very tiny color box next to each series name. Here’s the code that I am using:
Jon Peltier says
April –
There must have been some kind of border before you ran the code. Let’s try to hide the border:
Abdul Aydemir says
Hi y’all
I’m using the very first code of this thread. It works perfectly except the following situation: I have assigned two different colors to a similar category name:
Name 1: Water reducer
Name 2: PCE Water reducer
Unfortunately it takes the same color as the first name (category) also for the second name (category) although I defined other colors. What do I have to change to have the right colors assigned by the VBA code? I guess the check for the name/category is by “contains” and not by “exact” search. Thank you for your feedback.
Jon Peltier says
Hi Abdul –
That’s an easy thing to fix. You need to change this:
to this:
to make VBA find the cell that matches the category name exactly.
Randall says
Jon – when I copy in the Excel 2007 and later VBA from your post into the ThisWorkbook object in the VBA editor, select my chart and then run the macro, I immediately get an “Object doesn’t support this property or method” error. Is there more initial setup I need to do to make the code work for my chart?
Secondarily, I need to modify the code to look at the 3 right-most characters of the category name and compare those to the values in my cells that have the desired colors. In other words, if the category name ends in XYZ, I need the macro to go to the cell labeled XYZ in order to determine the color. Can you assist with that code modification?
Jon Peltier says
Randall –
You shouldn’t put the code into ThisWorkbook. Instead, right click on ThisWorkbook, select Insert > Module. This opens a new blank module, where you should paste the code.
This is covered in How To: Use Someone Else’s Macro. You may also benefit from reading VB Editor Settings.
Adam Meyer says
Thanks for this great tip! I’m running into trouble when defining rPatterns as a range that is populated by formulas. The list of categories needs to update automatically based on user selections.
I receive the Run-time Error ’91’ on line .Points(iCategory).Format.Fill.ForeColor.RGB = rCategory.Interior.Color when there are formulas in the range defined for rPatterns. This error does not happen when I just type the categories in manually.
Dieter van de Scheur says
Hi,
I would like to code an excel chart by category label color using VBA.
There are 3 categories (columns) and each has 12 series, however of the 12 series to the datatable I only want the category coloring to apply to series 3, 4, 7, 8, 10 and 12 of each column (the other series should not have any color, which I already took care of with excel formatting).
I initially tried the code below derived from this thread, but this only works if my coloring is to apply to consecutive series (e.g. 1 To 6),
I’m not skilled enough with VBA to get this done for the selection of series.
Can you please help?
Jon Peltier says
Dieter –
Untested, but try this:
Monica says
Use “On Error Resume Next”. This will remove the error related to .Points(iCategory).Format.Fill.ForeColor.RGB = rCategory.Interior.Color
Jon Peltier says
On Error Resume Nextwill remove the error, but it will not fix the problem. I assume the error occurs because the categories defined by the formulas do not exactly match the categories in the chart source data.vwolfe says
Has anyone gotten this to work with pie charts? or know what modifications I can make to get it to work with pie charts
Jon Peltier says
It works as is for pie charts, I just tested to make sure. Put the category labels into A1:A4 (or other range, if you adjust the code) and apply the desired colors. Select the chart and run the procedure.
Ethan says
Hi Jon,
Thanks a lot for providing this VBA. I’m using the below code lifted directly from above though have changed the relevant cell ranges. It works fine, except it formats columns in my chart for some series in a slightly different shade to the colours I have formatted my cells in (cells filled in the appropriate colour) Do you know why this might be; perhaps if there’s a way to adjust the code so it is based on specific RLG or Hex codes rather than the formatting of a cell (haven’t much experience with actually writing VBAs so I’m not sure)?
ub 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(“A6:A8”)
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
Jon Peltier says
Instead of
.ColorIndex(the Excel 2003 and older version of the routine), you should use.Color(the version for Excel 2007 and later)..ColorIndexis how the 56 colors of a workbook were specified in Excel 2003 and earlier. Starting in Excel 2007, we can access 16777216 colors, and we can refer to their RGB values using.Color.Celtia says
First of all, thank you for the awsome work you did here.
My question is pretty easy, but the solution is not :)
I have some pattern inside the colored cell, like dot or shaded. When I use the initial code, it doesn’t take into consideration the shaded or doted pattern inside the color.
It’s probably a quick fix, but could not find it.
Thank you
Jon Peltier says
Hi Celtia –
You’re correct. My code only applies the main fill color (the forecolor) of the cell to the plotted data. I use patterns only in extremely rare instances, so I have not included them in these routines. You would have do some experimenting (i.e., record some macros and examine the results) to see what you would need to read the pattern information from the cells and apply that information to the chart.
Celtia says
Thank you for your answer Jon. I’m completely a newbie in VBA but I will try.
Last question, would you mind to tell me how to use the same macro, if the data are in a stacked column, which means that I want the macro to apply to horizontal Axis Labels( Category ) not the Legend Entries (Series). I tried to change the macro to read the categories and not the series but it doesn’t work. I don’t know if I’m clear in my request. The code seems to work only for pie chart and simple histogram chart but not for stacked column chart.
Thank you very much for your help