VBA Conditional Formatting of Charts by Category Label

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 1Simple Conditional Chart Example 2

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

Although I prefer to use the formulaic approach above, sometimes it makes more sense to use VBA to apply formats. Suppose you have a sorted column chart, and the categories may be in any order. However, you want to color a category according to its name, so that Halliburton always is colored red and Enron is always blue.

The following protocol allows you to color the points in a series according to colors you’ve reserved for certain categories. The range below illustrates the data: range A1:A4 contains a list of the categories, with each cell filled with the desired color for that category’s bar in a chart. A6:B10 contains data for Chart 1, and A12:B16 contains data for Chart 2. (The colors in A1:A4 were defined using the Color Brewer utility described in my earlier post, Using Colors in Excel Charts.)

The charts made from the two ranges are not remarkable.


There is a simple VBA procedure that will apply the colors from the designated range to the appropriate points, based on their categories.

Excel 2003 and earlier:

Sub ColorByCategoryLabel()
  Dim rPatterns As Range
  Dim iCategory As Long
  Dim vCategories As Variant
  Dim rCategory As Range

  Set rPatterns = ActiveSheet.Range("A1:A4")
  With ActiveChart.SeriesCollection(1)
    vCategories = .XValues
    For iCategory = 1 To UBound(vCategories)
      Set rCategory = rPatterns.Find(What:=vCategories(iCategory))
      .Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex
    Next
  End With
End Sub

Excel 2007 and later:

Sub ColorByCategoryLabel()
  Dim rPatterns As Range
  Dim iCategory As Long
  Dim vCategories As Variant
  Dim rCategory As Range

  Set rPatterns = ActiveSheet.Range("A1:A4")
  With ActiveChart.SeriesCollection(1)
    vCategories = .XValues
    For iCategory = 1 To UBound(vCategories)
      Set rCategory = rPatterns.Find(What:=vCategories(iCategory))
      .Points(iCategory).Format.Fill.ForeColor.RGB = rCategory.Interior.Color
    Next
  End With
End Sub

Select a chart, and run the procedure above (press Alt+F8 to bring up the macro dialog, select the procedure, and click Run). The bars will change color according to their category names and the colored table in A1:A4.


To format by value, see VBA Conditional Formatting of Charts by Value.
To format by series name, see VBA Conditional Formatting of Charts by Series Name.

Peltier Tech Chart Utility

Comments

  1. 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?

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

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

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

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

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

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

     

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

  8. 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…

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

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

     

  11. Hi LEM -

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

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

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

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

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

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

  17. perfect. thanks for the quick response.
    ckz

  18. 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?

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

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

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

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

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

  24. What do the formulas look like?

  25. Jon Peltier

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

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

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

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

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

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

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

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

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

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

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

  36. 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
     
  37. 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!

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

  39. Jorge -

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

    With ActiveChart.SeriesCollection(1)

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

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

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

  43. Tyler -

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

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

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

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

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

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

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

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

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

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

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

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

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

  56. Daniel -

    Try something like this:

    Sub ColorByCategoryLabel() 
      Dim rPatterns As Range 
      Dim iCategory As Long 
      Dim vCategories As Variant 
      Dim rCategory As Range 
      Dim srs As Series
    
      Set rPatterns = ActiveSheet.Range("A1:A4") 
    
      For Each srs in ActiveChart.SeriesCollection
        If srs.ChartType = xlColumnClustered Then
          vCategories = srs.XValues 
          For iCategory = 1 To UBound(vCategories) 
            Set rCategory = rPatterns.Find(What:=vCategories(iCategory)) 
            srs.Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex 
          Next 
        End If
      Next
    End Sub
  57. 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.

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

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

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

  61. 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…

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

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

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

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

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

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

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

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

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

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

  72. 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?

  73. Ben -

    In general this approach will work for any chart type. You need to use the appropriate formatting syntax for the chart elements. Make sure the series uses the right range for its X values.

    What’s the text? If you need to handle this by numeric ranges, then simple text may not work. It may be better to try adapting this approach to your project:
    http://peltiertech.com/Excel/Charts/ConditionalChart1.html

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

  75. 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″

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

  77. 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,

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

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

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

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

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

  83. Derek -

    Check out my article on Chart Events in Microsoft Excel.

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

  85. Tobias -

    This code was written for the Excel 2003 color system. Try using .Color in place of .ColorIndex.

  86. You’re a genius!!
    Thanks a lot, it works perfectly :-)

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

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

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

  90. 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?

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

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

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

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

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

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

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

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

    Sub ColorByCategoryLabel()
      Dim rPatterns As Range
      Dim iCategory As Long
      Dim sCategory As String
      Dim iCell As Long
      Dim sCell As String
      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)
          sCategory = vCategories(iCategory)
          For iCell = 1 To rPatterns.Cells.Count
            sCell = rPatterns.Cells(iCell).Value
            If Left$(sCategory, Len(sCell)) = sCell Then
              Set rCategory = rPatterns.Cells(iCell)
              .Points(iCategory).Format.Fill.ForeColor.RGB _
                  = rCategory.Interior.Color
            End If
          Next
        Next
      End With
    End Sub
  99. 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…

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

    Else
      Selection.Interior.ColorIndex = -1
    End If
  101. 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

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

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

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

  105. Doris -
    Good point. My labels were completely unrelated, but if one label is contained with another, VBA may well apply the wrong formats.

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

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

    Sub ColorByCategoryLabel()
      Dim rPatterns As Range
      Dim iCategory As Long
      Dim vCategories As Variant
      Dim rCategory As Range
      Dim iSeries As Long
    ActiveSheet.ChartObjects("Chart 2").Activate
      Set rPatterns = ActiveSheet.Range("Ab59:Ab74")
      For iSeries = 1 To 17
      ActiveChart.SeriesCollection(2).Select
        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

    Thanks again for your help

    Lee.

  108. I think this is what you want:

    Sub ColorByCategoryLabel()
      Dim rPatterns As Range
      Dim iCategory As Long
      Dim vCategories As Variant
      Dim rCategory As Range
      Dim iSeries As Long
      ActiveSheet.ChartObjects("Chart 2").Activate
      Set rPatterns = ActiveSheet.Range("Ab59:Ab74")
      With ActiveChart.SeriesCollection(2)
        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
  109. 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

  110. Lee -
    What is the error description (not just error number)? Has rCategory been properly defined?

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

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

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

  114. “Object variable or with block variable not set”
    This means that rCategory isn’t defined. It must mean that
    Set rCategory = rPatterns.Find(What:=vCategories(iCategory))
    didn’t find a cell in rPatterns that matched the entry in vCategories(iCategory)

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

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

  117. Prateek -
    I’m sorry, I think it would take me half a day to get my head around that code.

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

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

  120. 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?

  121. 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” ?

  122. 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?

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

  124. This ancient tutorial shows how to ask the user for a scaling percentage, and then resize the pasted shape:
    http://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.

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

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

Subscribe without commenting

Trackbacks

  1. […] to see if anyone has any idea on this… it's driving me crazy! This blog post from Jon Peltier (VBA Conditional Formatting of Charts by Category Label – Peltier Tech Blog) is almost what I'm trying to do but instead of changing colour of series, I want to change to a […]

  2. […] using John Peltier's Formatting of charts by Category label code from VBA Conditional Formatting of Charts by Category Label – Peltier Tech Blog to format the colour of Column and Pie charts in Excel 2010. […]

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.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.

 

Site by: Dawud Miracle, Business Coach & WordPress Websites