VBA Conditional Formatting of Charts by Category Label

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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 Daniel Murray
Time: Tuesday, January 18, 2011, 11:25 pm

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

Comment from Daniel Murray
Time: Tuesday, January 18, 2011, 11:50 pm

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

Comment from Daniel Murray
Time: Wednesday, January 19, 2011, 10:16 pm

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

Comment from Daniel Murray
Time: Thursday, January 20, 2011, 1:22 am

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.

Comment from Daniel Murray
Time: Saturday, February 19, 2011, 1:47 pm

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.

Comment from Daniel Murray
Time: Thursday, March 10, 2011, 1:04 am

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

Comment from Daniel Murray
Time: Friday, March 11, 2011, 2:35 pm

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…

Comment from LEM
Time: Thursday, March 24, 2011, 10:30 am

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.

Comment from TG
Time: Friday, September 16, 2011, 4:49 pm

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.

Comment from TG
Time: Friday, October 7, 2011, 12:52 pm

Sorry for the late reply. This worked, thank you!

Comment from Kinric
Time: Thursday, October 27, 2011, 12:20 pm

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

Comment from Vivienne
Time: Thursday, November 24, 2011, 10:34 am

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

Comment from Vivienne
Time: Sunday, November 27, 2011, 3:49 pm

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

Comment from ben
Time: Thursday, January 5, 2012, 12:29 pm

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?