VBA Conditional Formatting of Charts by Category Label

by Jon Peltier
Monday, March 3rd, 2008
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.

I’ve got a couple of tutorial pages on my web site that deal with conditionally formatted charts. The trick is to set up ranges containing formulas for each of the conditions, so that if the condition for that range is met, the range contains the value to plot, otherwise it contains #N/A, which isn’t plotted in a line or XY chart, or just a blank or zero, which is plotted as a zero-thickness bar or column in a bar or column chart. You can read about them here:

Simple Conditional Chart Example 1 – Simple Conditional Chart Example 2

As with conditional formatting in the worksheet, the formatting you can achieve is limited only by your ability to construct formulas. Of course, in the chart, you are not limited to three conditional formats as you are in the worksheet (in Excel 2003 at least; the conditional formatting enhancements in Excel 2007 are mind-boggling).

Although I prefer to use the formulaic approach above, sometimes it makes more sense to use VBA to apply formats. Suppose you have a 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:

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

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.

Bookmark and share this entry:

Posted: Monday, March 3rd, 2008 under VBA.
Comments: 51

Comments

Comment from Pablo
Time: Friday, June 13, 2008, 12:06 pm

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?

Comment from Jon Peltier
Time: Friday, June 13, 2008, 12:52 pm

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

Comment from Arnaud
Time: Friday, October 17, 2008, 6:13 am

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.

Comment from Jon Peltier
Time: Friday, October 17, 2008, 7:04 am

Set rPatterns = ActiveSheet.Range(“A1:A4″)

Have you selected a chart? The procedure needs an active chart.

Comment from J
Time: Friday, November 21, 2008, 11:12 am

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.

Comment from Jon Peltier
Time: Friday, November 21, 2008, 12:13 pm

J –

If you are formatting more than one series in a chart you have to change this line:

With ActiveChart.SeriesCollection(1)

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 Sub

Comment from LEM
Time: Monday, November 24, 2008, 11:31 am

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

Comment from LEM
Time: Monday, November 24, 2008, 11:40 am

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…

Comment from Jon Peltier
Time: Monday, November 24, 2008, 12:03 pm

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 Sub

2. “.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

Comment from LEM
Time: Monday, November 24, 2008, 2:54 pm

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.ChartObjects
        oCht.Activate
        With 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 Sub

Comment from Jon Peltier
Time: Monday, November 24, 2008, 3:21 pm

Hi LEM -

Thanks for posting this. I’ve made one minor change to prevent seizures in those with a low threshold.

Comment from ckz
Time: Friday, December 5, 2008, 3:34 pm

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 Sub

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

Comment from Jon Peltier
Time: Friday, December 5, 2008, 5:10 pm

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 Sub

I find it best to reference everything, starting with the worksheet.

Comment from ckz
Time: Monday, December 8, 2008, 12:42 pm

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

Comment from ckz
Time: Monday, December 8, 2008, 12:48 pm

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

Comment from Jon Peltier
Time: Monday, December 8, 2008, 1:48 pm

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.

Comment from ckz
Time: Monday, December 8, 2008, 2:59 pm

perfect. thanks for the quick response.
ckz

Comment from LEM
Time: Thursday, January 8, 2009, 2:20 pm

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?

Comment from LEM
Time: Thursday, January 8, 2009, 3:10 pm

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

Comment from Jon Peltier
Time: Thursday, January 8, 2009, 3:18 pm

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.

Comment from Vanessa
Time: Monday, June 1, 2009, 2:04 pm

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!

Comment from Jon Peltier
Time: Monday, June 1, 2009, 3:22 pm

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 “”.

Comment from Vanessa
Time: Monday, June 1, 2009, 3:35 pm

Yes, it’s a bar chart. I tried this, but instead of #N/A it now shows zeroes!

Comment from Jon Peltier
Time: Monday, June 1, 2009, 3:41 pm

What do the formulas look like?

Comment from nong
Time: Saturday, June 6, 2009, 7:11 pm

Jon Peltier

i want to put series color(different solid color to fill 4 series) could you show me the VBA code please

Comment from Jon Peltier
Time: Saturday, June 6, 2009, 7:29 pm

Nong -

Do you want this: VBA Conditional Formatting of Charts by Series Name?

Comment from Danièle
Time: Thursday, July 2, 2009, 8:01 am

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

Comment from Jon Peltier
Time: Thursday, July 2, 2009, 9:14 am

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.

Comment from Danièle
Time: Thursday, July 2, 2009, 7:36 pm

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

Comment from Jon Peltier
Time: Thursday, July 2, 2009, 8:23 pm

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.

Comment from Danièle
Time: Thursday, July 2, 2009, 8:49 pm

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

Comment from Samreen
Time: Monday, July 13, 2009, 3:01 pm

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!

Comment from Jon Peltier
Time: Monday, July 13, 2009, 3:09 pm

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.

Comment from Steven
Time: Monday, July 20, 2009, 8:14 am

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.

Comment from Jon Peltier
Time: Monday, July 20, 2009, 8:59 am

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)”.

Comment from Steven
Time: Monday, July 20, 2009, 10:27 am

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.

Comment from Jon Peltier
Time: Monday, July 20, 2009, 10:39 am

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

Comment from Steven
Time: Monday, July 20, 2009, 11:46 am

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!

Comment from Jorge
Time: Sunday, December 13, 2009, 5:30 am

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

Comment from Jon Peltier
Time: Sunday, December 13, 2009, 2:27 pm

Jorge -

In the page you’ve cited, the chart has to be selected. Note the line:

With ActiveChart.SeriesCollection(1)

Comment from Jorge
Time: Sunday, December 13, 2009, 3:27 pm

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 Sub

Which 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

Comment from Jon Peltier
Time: Sunday, December 13, 2009, 5:46 pm

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.

Comment from tyler
Time: Friday, February 12, 2010, 1:23 pm

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

Comment from Jon Peltier
Time: Friday, February 12, 2010, 4:20 pm

Tyler -

Make sure vCategories(iCategory) (the ith X axis label) actually exists in the rPatterns range.

Comment from Paul PW
Time: Thursday, May 20, 2010, 3:29 pm

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

Comment from Jon Peltier
Time: Thursday, May 20, 2010, 8:31 pm

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

Comment from Felipe
Time: Monday, August 2, 2010, 3:18 pm

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

Comment from Felipe
Time: Monday, August 2, 2010, 3:33 pm

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

Comment from Jon Peltier
Time: Wednesday, August 4, 2010, 10:29 am

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.

Comment from Sandy
Time: Friday, August 6, 2010, 3:01 pm

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.

Comment from Jon Peltier
Time: Saturday, August 7, 2010, 10:27 am

Sandy – What line of code is highlighted? That should give a strong hint about which object or variable is not defined.

Write a comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.

If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Read the PTS Blog Comment Policy.

Excel Chart Add-Ins | Training | Custom Solutions | Charts and Tutorials | PTS Blog

Main menu:

Peltier Tech
Chart Add-Ins

Excel Dashboards

Subscribe

Site search

Subscribe

Site search

Recent Posts

Recently Commented

Popular Posts

Archive

Categories