VBA Conditional Formatting of Charts by Series Name

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 chart with several series, and the series 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 series in a chart according to colors you’ve reserved for each series. The range below illustrates the data: range A1:A4 contains a list of the series names, with each cell filled with the desired color for that series’ data in a chart. A6:E10 contains data for Chart 1, and A12:E16 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 seriess, based on their names.

Excel 2003 and earlier:

Sub ColorBySeriesName()
  Dim rPatterns As Range
  Dim iSeries As Long
  Dim rSeries As Range

  Set rPatterns = ActiveSheet.Range("A1:A4")
  With ActiveChart
    For iSeries = 1 To .SeriesCollection.Count
      Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, _
          LookAt:=xlWhole)
      If Not rSeries Is Nothing Then
        .SeriesCollection(iSeries).Interior.ColorIndex = _
            rSeries.Interior.ColorIndex
      End If
    Next
  End With
End Sub

Excel 2007 and later:

Sub ColorBySeriesName()
  Dim rPatterns As Range
  Dim iSeries As Long
  Dim rSeries As Range

  Set rPatterns = ActiveSheet.Range("A1:A4")
  With ActiveChart
    For iSeries = 1 To .SeriesCollection.Count
      Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, _
          LookAt:=xlWhole)
      If Not rSeries Is Nothing Then
        .SeriesCollection(iSeries).Format.Fill.ForeColor.RGB = _
            rSeries.Interior.Color
      End If
    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 series names and the colored table in A1:A4.


To format by category label, see VBA Conditional Formatting of Charts by Category Label.
To format by value, see VBA Conditional Formatting of Charts by Value.

 

Peltier Tech Charts for Excel

Comments

  1. Jon – I’m not a huge fan of the color choices, but it’s a nice trick that is pretty straightforward. Nice work!

  2. Tony – Thanks for the comment. The colors are the bright versions, intended for lines and markers, rather than the lighter versions intended for fills like these. It’s a snap to update the colors since I still have the VBA procedures in the workbook, but the thought of uploading a dozen or more pictures….

    The trick is pretty easy, because you can much more easily change a cell color than figure out which color index to plug into the code. I can even trust my users to do that part right.

    I don’t know what’s up with that code box; it keeps forgetting the indentation settings.

  3. Jon,

    Each of your three recent entries on chart formatting techniques are very practical. I can see using the macros at work to insure that the chart colors remain uniform across different documents.

    On another note, to keep the indentation settings for the VBA code, maybe the “pre” tag would work . .

    Your code here

    You can format it in your CSS template similar to any other tag . .

    pre {
    padding: 5px;
    border: 1px solid #000000;
    background-color: #FBFAF5;
    font-family: verdana, georgia, times new roman, times, serif;
    font-size: 11px;
    color: #000000;
    }

    The downside is that it may not pick up the color changes within the code.

    — Just thought I’d suggest it.

  4. Jon,

    Sorry, it looks like I didn’t pick up the “pre” tag. Hopefully this works . .

    < pre > Your code here < / pre >

  5. John –

    Thanks for the suggestion about the pre tags. I’ve actually used pre tags about since I started my first web site in 2001. Unfortunately the WodPress editor takes liberties with the code you enter, and sometimes steps on tags, especially pre and br. Grr. The code lister I use is the one Dick K uses on Daily Dose of Excel, and it seems okay, but sometimes the indents go away, and I need to figure out what to do about the garish colors.

  6. Jon, I am using your code to format a graph with the option of selecting 19 colours.
    It worked successfully earlier today but now has an error message of “Object Variable or With block variable not set”.
    I cannot figure out what is causing this. Has anyone came across this problem before?
    The code is exactly what I require.

    Here is the code I used.

    [vb]Sub ColorBySeriesName()
    Dim rPatterns As Range
    Dim iSeries As Long
    Dim rSeries As Range

    Set rPatterns = Sheets(“Tools”).Range(“A1:A19”)
    With ActiveChart
    For iSeries = 1 To .SeriesCollection.Count
    Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name)
    .SeriesCollection(iSeries).Interior.ColorIndex = rSeries.Interior.ColorIndex
    Next
    End With
    End Sub[/vb]

    Regards,

    Davie

  7. Davie – You didn’t indicate which line produced the error. The error you cited could occur if the active workbook has no sheet names “Tools”, and also if there’s no active workbook at all, or if a chart isn’t selected when the code first runs, or if there is no SeriesCollection(iSeries).

    Also note that you can get formatted code in your comment by enclosing the code in VB tags, like these without spaces between [ and ]:

    [ v b ]
    Your code here
    [ / v b ]

    I’ve applied VB tags to the code in your comment above.

    These [ v b ] and [ / v b ] tags no longer work.

  8. Jon, the error occurs on line 10. The active workbook has a sheet “Tools”, I have selected the chart as the ActiveChart before I run the macro.

    I want ultimately to run this in the formatting table macro.

    Davie

  9. Davie – What kind of chart is it? Line 10 is attempting to change

    .SeriesCollection(iSeries).Interior.ColorIndex

    but some kinds of series have no Interior (line or XY charts, for example), so this line will fail. You would have to modify the code to adjust .MarkerForegroundColorIndex, .MarkerBackgroundColorIndex, and .Border.ColorIndex for such a series.

  10. Jon, it is a stacked column graph. I tried “MarkerBackgroundColorIndex but still got the same error message.

    Davie

  11. Jon, i recreated the graph and it is working ok. Don’t know what the problem was. Thanks for your help.

    Davie

  12. hi Jon

    is it possible to apply this to cells that are conditionally formatted

    the color of my series are determined by other financial information therefore i use conditional formatting to assign colors

    i have had no luck so far

    thanks
    tom

  13. Tom – VBA only knows the base formatting for a cell, not what is applied by conditional formatting. You would have to write your formatting conditions into the code that selects the colors for the chart.

  14. Drandon Campbell says:

    Jon, as usual your excel knowledge has helped me tremendously in the past. I thank you very much. I get the error “Object Variable or With block variable not set” on the line “.SeriesCollection(iSeries).Interior.ColorIndex = rSeries.Interior.ColorIndex”. I am using a Bubble Chart. Anyway I can do this same color principal on this type of chart?

    Thank you.

  15. Drandon –

    I just tested it, and the code as written works fine in a bubble chart. The only way I replicated your error in the indicated line is if the series name isn’t listed in the range where the colors are defined. Check for a typo or that your VBA range includes all the formatted cells. In other words, this fails:

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

    if you have more than four series.

  16. Drandon Campbell says:

    I have 10 Payers (Top 10 – will not change)
    Here is my code –

    Sub setColor()
    Dim rPatterns As Range
    Dim iSeries As Long
    Dim rSeries As Range

    Set rPatterns = Sheets(“LOOKUPS”).Range(“K51:K60”)
    ActiveSheet.ChartObjects(“Chart 18”).Activate
    With ActiveChart
    For iSeries = 1 To .SeriesCollection.Count
    Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name)
    .SeriesCollection(iSeries).Interior.ColorIndex = rSeries.Interior.ColorIndex
    Next
    End With
    End Sub

    Thanks Jon.

  17. That’s the code. Things to check:

    + Is the correct chart being activated?
    + Is K51:K60 the correct format lookup range?
    + Does the chart have the right number of series?
    + Are the series names exactly as found in the lookup list (no unintentional spaces, etc.)?
    + Does the code fail on the first loop, or do some series get the new formatting applied?

  18. Drandon Campbell says:

    There was actually two more series than I thought (two are set error bars- vertical and horizontal). I reset K60 to K62 and set the last two cells to no color. Still didn’t work – same error. I think my issue may be that my Payers actually constitute a total of 50 or so. K51:K62 are referencing the chart data (=’Bubble Graph’!B16 and so on). When I change the year the top 10 payers change. I don’t think I have enough colors for all 50 Payers :)

    Oh well. Thanks a lot though.

  19. If a series name isn’t found, Excel can’t find a cell to select a color from. You need to trap for this error:

      If Not rSeries Is Nothing Then
        .SeriesCollection(iSeries).Interior.ColorIndex = rSeries.Interior.ColorIndex
      End If
  20. Jon,

    I copied your example and it worked no problem. But, when i try to apply it to my sheet i get the same error as others above. Does it not work if the color reference cells are formulas, not values?

  21. Ariella –

    There are several reasons that the code may fail. It helps me to debug if you can indicate the error number and description and the line of code which is highlighted when the error occurs.

    I have added the lines of code from a couple comments above, which bypasses the formatting line if the series name is absent from the lookup list.

    If the lookup list is generated by formulas, you have to make sure that the formulas produce the series names exactly. A stray space character or a character with mismatched capitalization will prevent matches.

  22. Can the above code be used for pie charts?

    I have a worksheet where data is entered. This data is copied to a hidden worksheet where the chart creates its datarange.

    On the page the user enters the data, I have a button for the user to view the charts.

    When the comandbutton is clicked – I tell the program to hide any rows with values of zeroes in the hidden worksheet so that the only information that is charted has a value.

    I want to add code to assign the correct colors to each category label.

    Here is my code… I am far from a programmer, and know little about the correct language to use for VBA.

    Private Sub CommandButton3_Click()
    Dim rng As Range
    For Each rng In Worksheets(“ChartData”).Range(“VolumeRange”)
    If rng.Value = 0 Then
    Worksheets(“ChartData”).Rows(rng.Row & “:” & rng.Row).Hidden = True
    End If

    Sub ColorBySeriesName()
    Dim rPatterns As Range
    Dim iSeries As Long
    Dim rSeries As Range

    Set rPatterns = ActiveSheet.Range(“A1:A14”)
    With ActiveChart
    For iSeries = 1 To .SeriesCollection.Count
    Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name)
    If Not rSeries Is Nothing Then
    .SeriesCollection(iSeries).Interior.ColorIndex = _
    rSeries.Interior.ColorIndex
    End If
    Next
    End With
    End Sub

    Next

    Application.ScreenUpdating = False
    Sheets(“Water Table”).Visible = False
    Sheets(“Pie Charts”).Visible = True
    Sheets(“Pie Charts”).Select
    End Sub

  23. ckz –

    The technique works just fine for a pie chart. But not this particular implementation. A pie chart has but a single series, plus a variety of categories. Formatting by series name will not help, but formatting by category should do what you want. See my related post VBA Conditional Formatting of Charts by Category Label.

    However, you can’t just drop a sub into the middle of another sub and expect it to work. Put the sub from this site into a regular code module, and call it from byt event procedure (CommandButton3_Click) by simply typing its name in a line of code.

    Then make sure you reference the appropriate sheet and range for the color lookup range and the appropriate chart.

  24. I have already created an excel tab as a template for a report, with severa charts, and I have a macro that will replicate the pages.
    Now I want to write a macro to replicate the entire page, including charts.
    Is there a way I can view the proerties of a chart already formated, so that I can copy the properties into a vba module to replicate the chart, without having to recreate the chart while recording a macro in order to capture the coding of the formatting that I am applying the chart being recorded?

  25. Mikael –

    Make a template, that is a worksheet or workbook with all the ranges and charts the way they need to be for your report. Then simply paste the new data values into a copy of the template. The ranges (all the formulas) will update, and so will the charts.

    Save the workbook as a separate Excel template (XLT file), then with the program, insert a new sheet based on the template. Step two is pasting in the data. There need not be a step three.

    Trying to do this property by property in a chart is a daunting task. There are so many properties that must be considered, some properties exist for only certain chart types, or for certain axis types.

  26. I am trying your code for a number of line chart where I want the colors to be the same for each line series in Excel 2003. Your script worked for an area chart, but I needed to modify it for a line chart.

    I tried to modify your code, but get an error at .SeriesCollection(iSeries).Border.ColorIndex = rSeries.Border.ColorIndex

    the error is “The object doesn’t support this property or method. ”

    Thanks for your help

    Sub ColorBySeriesName()
    Dim rPatterns As Range
    Dim iSeries As Long
    Dim rSeries As Range

    Set rPatterns = ActiveSheet.Range(“A1:H1”)

    With ActiveChart
    For iSeries = 1 To .SeriesCollection.Count
    Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name)
    If Not rSeries Is Nothing Then
    .SeriesCollection(iSeries).Border.ColorIndex = rSeries.Border.ColorIndex
    End If
    Next
    End With
    End Sub

  27. John –

    Two problems with using rSeries.Border.ColorIndex

    1. A range has a Borders property, not a Border property.

    2. If a cell has more than one color among its borders, rSeries.Borders.ColorIndex returns Null, because there is no single color index that applies. You could use rSeries.Borders(xlEdgeTop).ColorIndex, but that’s a bit hard to work with. Also, it’s harder for a user to set border colors than interior colors. I would recommend you stick with the interior color and rSeries.Interior.ColorIndex

  28. Previously, you mentioned that the procedure won’t work with a line chart. What procedure would you write to change a line chart.

    Davie – What kind of chart is it? Line 10 is attempting to change

    .SeriesCollection(iSeries).Interior.ColorIndex

    but some kinds of series have no Interior (line or XY charts, for example), so this line will fail. You would have to modify the code to adjust .MarkerForegroundColorIndex, .MarkerBackgroundColorIndex, and .Border.ColorIndex for such a series.

  29. John –

    Here is what I would use to color the lines and markers in a line chart:

    Sub ColorBySeriesName()
      Dim rPatterns As Range
      Dim iSeries As Long
      Dim rSeries As Range
      Dim iColorIndex As Long
    
      Set rPatterns = ActiveSheet.Range("A1:H1")
    
      With ActiveChart
        For iSeries = 1 To .SeriesCollection.Count
          Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name)
          If Not rSeries Is Nothing Then
            iColorIndex = rSeries.Interior.ColorIndex
            With .SeriesCollection(iSeries)
              .Border.ColorIndex = iColorIndex
              .MarkerForegroundColorIndex = iColorIndex
              .MarkerBackgroundColorIndex = iColorIndex
            End With
          End If
        Next
      End With
    End Sub
  30. Thanks for your help with this.

    I am getting an error at the line “iColorIndex = rSeries.Border.ColorIndex”. “Object does not support property or method.”

  31. Doh! That should be

    iColorIndex = rSeries.Interior.ColorIndex

  32. Thanks for your help.

  33. Another query, I would like the markerstyle to vary between the series on a particular chart like the color varies, but be constant across charts for each series.

  34. You need to include the code to manipulatee the marker style:

    Sub ColorAndMarkerBySeriesName()
      Dim rPatterns As Range
      Dim iSeries As Long
      Dim rSeries As Range
      Dim iColorIndex As Long
    
      Set rPatterns = ActiveSheet.Range("A1:A4")
      With ActiveChart
        For iSeries = 1 To .SeriesCollection.Count
          Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name)
          If Not rSeries Is Nothing Then
            iColorIndex = rSeries.Interior.ColorIndex
            With .SeriesCollection(iSeries)
              .Border.ColorIndex = iColorIndex
              .MarkerForegroundColorIndex = iColorIndex
              .MarkerBackgroundColorIndex = iColorIndex
            End With
            Select Case LCase$(rSeries.Offset(, 1).Value)
              Case "square"
                .SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleSquare
              Case "circle"
                .SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleCircle
              Case "triangle"
                .SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleTriangle
              Case "diamond"
                .SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleDiamond
            End Select
          End If
        Next
      End With
    End Sub
     
  35. Hi Jon,
    I’m using this code in a worksheet and it works fine except for one thing: When a field in the designated row with colors consists of only one character, it does not recognise that color in the chart. I tested this by adding an extra character and all’s fine. But as you may guess, I really need it to consist of just one character.
    Any thoughts on this one? Help is much appreciated!
    Thanks,
    Steven.

    Sub ColorBySeriesName()
    Dim rPatterns As Range
    Dim iSeries As Long
    Dim rSeries As Range

    Set rPatterns = ActiveWorkbook.Worksheets(“Schema Aangepast Ursulaklooster”).Range(“A2:A49”)
    With ActiveChart
    For iSeries = 1 To .SeriesCollection.Count
    Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name)
    If Not rSeries Is Nothing Then
    .SeriesCollection(iSeries).Interior.ColorIndex = _
    rSeries.Interior.ColorIndex
    End If
    Next
    End With
    End Sub

  36. Steven –

    I’ve changed Halliburton to H and Enron to E, in both the chart source data range and in the range that defines the colors. The code updates the chart properly.

  37. Weird, there must be something wrong with my worksheet. I found out in the meantime that the letter ‘r’ DOES work, but the letters p/w/m/f/g do not. I did not check other letters though and these are the only single character fields in the datasheet. I’ve checked for loose spaces or any inconsistencies between the sourcefields and the chart-data but found nothing out of the ordinary.

    I’m using excel 2000 and defined a lot of my own colors, but this can hardly be the cause. Besides, when I add any character to these fields, the code works perfectly. The only similarity between the dead records i can find is the fact that they’re only one character long. But since the ‘r’ does work, I’m rather at a loss as to what the problem could be. Perhaps it’s best to start from scratch. I’ll let you know if that approach works.

    BTW the chart data is imported through a pivot table from MS access. Could this be at the heart of the problem? Keeping in mind that the code does work for 43 out of 48 records and there’s no inconsistencies between the fields…

    Many thanks for your quick replies!!!

  38. Steven –

    Sounds like gremlins in the worksheet. What if you use a single character plus a space, like “p “? Do you have similar sheets that don’t have this problem? I hate to have to suggest it, but it might be worthwhile to recreate the worksheet from scratch.

  39. Well, this thing is keeping me awake at night. I’m sorry I keep bothering you like this, but here’s something weird you might find interesting:

    If you create the following cells A1-A6 in this order: e/ei/hi/h/ri/r and add some colors and make that the color-range. Then make a sheet with two series and give them some values, with rownames corresponding to the colorrange, but not necessarily in that order (like your example on the top of the page). Now if you make a chart based upon that data and run the code, the result is that the rows that start with the same letter get the same color (at least in my 2000 version).

    If you change the sorting of the color-range to ei/e/h/hi/r/ri and run the code on the same chart again, then the colors are projected as they should be. If you add any other character (or space) everythings allright no matter the order of the data. I’ve tried this several times in new sheets, workbooks and with different data and I get this result all the time.

    So it looks like a consistant error, but there’s something weird. Using the data above, in the first two cells the single character comes before the double character cell, but in the subsequent cells this is the other way around. This is simillar to the odd ‘r’ in my previous post. This seems to work only for the first two cells, for if you change the order of the last two (ri/r) they get the same color again. If you mess about with it some more there are more inconsistancies for which I have no explanation (and no wish to type here :) ).

    I wonder if you can recreate this phenomenon and hope you have another smart solution to this problem.
    Many thanks for the time you spent on my questions!

  40. Steven –

    Ah, what we need to do (and I should have done) was to specify that Excel should search for cells whose whole content matches the search string:

    Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, LookAt:=xlWhole)

    I’ll change the post.

  41. Excellent!!! Works like a charm!
    I’m gonna buy you a cup of coffee!!!
    :D

    THANKS!!

  42. Hi Jon,

    Was going through your articles on parallel coordinates. In that article you mentioned that you formatted all the lines to light grey. Did you format this line by line or is there a way to format all lines at one time – Would like to avoid VBA if possible.

    paresh

  43. Hi Paresh –

    You can’t format the lines all at once. You can format one, then select the next, and press the F4 shortcut key (“Repeat Last Action”). This works nicely in 2003, repeating all changes from the trip to the formatting dialog. In 2007 it repeats at most the last one action (like line color or marker type), and sometimes none at all.

  44. Hi Jon,

    Thanks.

    But a parallel coordinate chart would have a large number of line and changing it one by one using F4 key would be tedious. Maybe the only alternative [ apart from using VBA ] is to use the first option in chart styles which give monchromatic graphs. Another alternative I have tried is to create chart template where say five lines have the same color and tried to use it for the new chart – works but only for five series for the sixth the color comes again.

    paresh

  45. Hi Jon

    Continued my research on the subject and this is what I discovered;

    a. maximum number of series in a chart is 255. This will seriously limit the use of parallel coordinates in excel as one would expect analysing hundreds of items at one time.

    b. An easy way to create a line chart with one color is ;
    1. create a line chart with one series
    2. modify the line as per requirements; color, thickness.
    3. Copy the series in the chart using control c
    4. paste it as many times as you want using contol v [ the 255 limit will apply]
    5. save the chart as template

    In the future whenever you draw a chart you can change the chart type to the saved template — you will have a line chart with one color.

    If the limit was not there, one could have visualised the use of parallel coordinates in excel with incredible scope using the filter functionality.

    Anyway time for an article on phd on chart templates.

  46. The F4 approach isn’t too tedious. Start with series 1, format it, click the up arrow to select series 2, click F4, repeat up arrow and F4 as required.

    If you want to use a template, you need to format as many series as you will need. not just five.

  47. Paresh – How do you accomplish steps 3 and 4? You can’t select a series and copy it.

  48. Follow up.

    Paresh has sent me a chart offline which he constructed by formatting one series, then selecting it, copying it using Ctrl+C, and pasting it using Ctrl+V. This functionality is new in Excel 2007, and is applicable to more than just one series. It seems if you copy any other chart element besides a single series, pasting duplicates all series in the chart.

    Similar behavior exists in 2003: copy the chart area of a chart, select the same or a different chart, and paste to adds the data of the copied chart to the active chart. I had never noticed this in 2007: if it’s a feature it’s a useful one. If it’s a bug, it’s still a useful one.

  49. Jon, just found the code you listed above and it has been a TREMENDOUS help, thanks.

    I’m looking through the conversation and I don’t believe the following question has been asked…

    In addition to changing the colors of the interiors of the bars in my chart, I’d like it to be able to change the color of the label font as well. I’m putting the labels at the base of the bars. One of my bars is set to show up black, so obviously a label with black font won’t show. I’m tried variations in my code to get it to pick up the font color in addition to the background color in my rPatterns range, but so far no luck.

    Any ideas on this? Thanks much, I’m far from a programmer myself but am pretty good at adapting code.

  50. Joel –

    Color the font in the cells the color you want the label text to be. Then right after this line:

            .SeriesCollection(iSeries).Interior.ColorIndex = _
                rSeries.Interior.ColorIndex

    insert this:

            .SeriesCollection(iSeries).DataLabels.Font.ColorIndex = _
                rSeries.Font.ColorIndex
  51. Thanks so much again!

  52. I was so happy to discover this code today and can see it being very useful. However, when I run a macro (ColorBySeriesName or ColorByValue) the colors that get implemented don’t always match the colors I’ve applied to my reference cells (A1:A4). Sometimes the colors are close, other times they are way off (completely different hue, or more often it seems a shade of gray). This occurs whether I use colors selected from theme or from “more colors.” I did create two custom color themes today, but I don’t see how that could affect the ColorBy macros because isn’t the vba taking the ColorIndex from the reference cells and applying that directly to the chart features?

  53. Stacey –

    Themes, eh? Sounds like 2007. I have yet to come to grips with the 2007 color system, and with the formatting commands for adding color to chart elements in 2007. Probably it would work better if the code is adjusted to measure the RGB of the filled cell instead of its colorindex or theme identities, then apply this RGB to the chart element.

    Some day I’ll take a crack at it.

  54. I am new to Excel VBA Programming. I have to write a VBA code which would copy the complete Chart format and style and then Replicate in other Worksheets and workbooks the same Format and style, but with different Data Source. (The Chart is defined by user manually, and i have to get the settings of this Chart).
    If anyone knows please help me in completing this task!!!

    ‘Thanks in Advance!!!

  55. Sri –

    It would probably be easier to copy the chart, paste it into the new sheet, and series-by-series reassign the data.

  56. Hey Jon,

    Thanks for the fast reply!!!

    Currently i am doing this work manually Chart by Chart… and it takes a lot of time.

    I was looking for automating this because i have almost 60 to 70 Charts in each workbook. Like this i have several workbooks to change. Currently i am working in reporting department of a company.

  57. Sri –

    If the chart isn’t too complicated, select the chart area of the chart with the formatting you want, copy it, select the chart that you need to format, and use Paste Special > Formats.

  58. yeah,I just thought you may possibly need to know that your blog is messed up when you view it on my iphone. I?m not positive if it has something to try and do with my telephone?s browser or your website? just saying…

  59. Hi Karyn –

    I’ve made no effort to make a mobile version of my blog. That said, my Droid displays my website with a minimum of distortion. It’s probably an incompatibility with the iPhone browser.

  60. I was trying to find such function in Excel for a real while and could never achieve it ! Thanks !!
    However, I had the 2007 problem that the color were not properly picked-up.
    Also, even though I understand almost nothing to what I am doing, I found a crosoft page describing the iColorIndex function, where they actually say you can use iColor instead, what I did and works perfectly…
    Thank you again for the code !

  61. This code is exactly what I need but I can’t get it to work. I have a chart with a list of categories that I’d like to code. The categories are in sheet “seriescolors” and each cell has been colored with the color I’d like the series. The chart is in a sheet called “exhibit”.

    Dim rPatterns As Range
    Dim iSeries As Long
    Dim rSeries As Range

    ‘ Set location of category names and their colors
    Set rPatterns = ActiveWorkbook.Worksheets(“seriescolors”).Range(“A1:A19”)

    /*Then there’s code that generates the chart*/

    With ActiveChart

    For iSeries = 1 To .SeriesCollection.Count
    Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, LookAt:=xlWhole)

    .SeriesCollection(iSeries).Interior.ColorIndex = rSeries.Interior.ColorIndex
    Next

    If Not rSeries Is Nothing Then
    .SeriesCollection(iSeries).Interior.ColorIndex = rSeries.Interior.ColorIndex
    End If
    End With

    It gives me an error at “.SeriesCollection(iSeries).Interior – “Object Variable or With Block Not Set” and the “iSeries” is coming up as 1. Any ideas what might be wrong here? Let me know if I can give more detail. Thanks!!!

  62. You’ve misplaced the If/Then statement

    With ActiveChart
      For iSeries = 1 To .SeriesCollection.Count
        Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, LookAt:=xlWhole)
        If Not rSeries Is Nothing Then
          .SeriesCollection(iSeries).Interior.ColorIndex = rSeries.Interior.ColorIndex
        End If
      Next
    End With
  63. Jon,

    I can’t thank you enough for the help on your site. I have successfully built the Gantt Chart for Repeated Tasks and now need the bar for each task to be a different color based on selections set by user. (In other words, I do not want to individually format each bar)

    I’ve attempted the VBA format by Series Name as well as by Category Label and haven’t gotten anywhere. I was thinking it may be due to the layers of different series in the Gantt chart? Anyhow, here is the current code I’m working with (one Sheet named DATA and one Chart named IA IT PROJECTS):

    Sub ColorBySeriesName()
    Dim rPatterns As Range
    Dim iSeries As Long
    Dim rSeries As Range

    Set rPatterns = Sheets(“DATA”).Range(“C19:C25”)
    ActiveSheet.ChartObjects(“IA IT PROJECTS”).Activate
    With ActiveChart
    For iSeries = 1 To .SeriesCollection.Count
    Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, LookAt:=xlWhole)
    If Not rSeries Is Nothing Then
    .SeriesCollection(iSeries).Interior.ColorIndex = rSeries.Interior.ColorIndex
    End If
    Next
    End With
    End Sub

    I had first color coded the 7 task names (AKA Ambulance 1, 2, 4, 7 from your charting tutorial) but thought maybe I should color code the associated number – neither worked.

    Currently, this is my error: “Run-time error ‘5’: Invalid procedure call or argument”

    As you can probably guess, this is my first experience with VBA.

    Thank you in advance.

    Sincerely,
    Leah

  64. Leah –

    “Neither worked”… The code ran for me, though it didn’t match the colors exactly. Do the labels in the patterns range exactly match the series names (spelling and capitalization)?

    Excel 2007+ uses different chart formatting syntax than I wrote about.

    Try this variation:

    Sub ColorBySeriesName()
      Dim rPatterns As Range
      Dim iSeries As Long
      Dim rSeries As Range
      Dim MyChart As Chart
    
      Set rPatterns = Sheets("DATA").Range("C19:C25")
      Set MyChart = Sheets("DATA").ChartObjects("IA IT PROJECTS").Chart
      With MyChart
        For iSeries = 1 To .SeriesCollection.Count
          Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, LookAt:=xlWhole)
          If Not rSeries Is Nothing Then
            .SeriesCollection(iSeries).Format.Fill.ForeColor.RGB = rSeries.Interior.Color
          End If
        Next
      End With
    End Sub
  65. hi jon,

    i am making a gnatt chart over a 24 hr period, i am tring to colour the bars based on a word i put in a cell, i.e peoples names, and what they are doing and for how long

    i.e cleaning
    waiting
    off these are in coloum e. i have put the cells onto the worksheet and tried using your example above changing the range to where i have put the coloured cells and the jobs, however i cant work why it wont change the bars to the colours.

    any help would be grateful

    richie

  66. Hi Jon,

    Thank you for all of this wonderful information. You have quite the gift.

    I was trying to apply the 2007-color version to all charts and sheets in the workbook (not just the active sheet). Being a VBA novice, I took a (working) variant of your macro:

    Sub ColorBySeriesName()
      Dim rPatterns As Range
      Dim SourceRangeColor As Long
      Dim SourceRange As Range
      Dim MyChart As Chart
    
        Set rPatterns = Sheets("Color_Scheme").Range("A1:A18")
          
          With ActiveChart
          For SourceRangeColor = 1 To .SeriesCollection.Count
            Set SourceRange = rPatterns.Find(What:=.SeriesCollection(SourceRangeColor).Name, LookAt:=xlWhole)
            If Not SourceRange Is Nothing Then
              .SeriesCollection(SourceRangeColor).Format.Fill.ForeColor.RGB = SourceRange.Interior.Color
              .SeriesCollection(SourceRangeColor).Format.Line.ForeColor.RGB = SourceRange.Interior.Color
              
          End If
        Next
      End With
    End Sub

    and tried to change it this way:

    Sub ColorBySeriesName()
      Dim sht As Worksheet
      Dim oChart As ChartObject
      Dim MySeries As Series
      Dim rPatterns As Range
      Dim SourceRangeColor As Long
      Dim SourceRange As Range
      Dim MyChart As Chart
    
      For Each sht In ActiveWorkbook.Worksheets
       For Each oChart In ActiveSheet.ChartObjects
        For Each MySeries In oChart.Chart.SeriesCollection
         Set rPatterns = Sheets("Color_Scheme").Range("A1:A18")
          
          For SourceRangeColor = 1 To .SeriesCollection.Count
            Set SourceRange = rPatterns.Find(What:=.SeriesCollection(SourceRangeColor).Name, LookAt:=xlWhole)
            If Not SourceRange Is Nothing Then
              .SeriesCollection(SourceRangeColor).Format.Fill.ForeColor.RGB = SourceRange.Interior.Color
              .SeriesCollection(SourceRangeColor).Format.Line.ForeColor.RGB = SourceRange.Interior.Color
            End If
          Next
         Next MySeries
        Next sht
      Next oChart
    End Sub

    The error I get is “Compile Error: Invalid or unqualified reference,” and the VBA editor highlights .SeriesCollection in the “For SourceRangeColor” line.

    If you’re not too busy, I would love to learn what I’m doing wrong, and how to fix it.

    Thank you again,
    Pat

  67. The immediate problem is that .SeriesCollection isn’t defined in the code following “For Each MySeries…”. So before “For SourceRangeColor” insert

    With oChart.Chart

    and after the associated “Next” insert

    End With

    You’ll find in general that using descriptive variable names, like iSeries, is better than a confusing variable name like SourceRangeColor. The variable is looping series, not colors or ranges.

  68. Jon,

    Thank you for such a prompt reply! I applied your suggestions (including changing the offending variable names) and made one minor change (replaced activesheet with sht).

    Here is the edited macro for posterity’s sake. Thank you once again!

    Pat

    Sub ColorBySeriesName()
      Dim sht As Worksheet
      Dim oChart As ChartObject
      Dim MySeries As Series
      Dim rPatterns As Range
      Dim iSeries As Long
      Dim rSeries As Range
      Dim MyChart As Chart
    
      For Each sht In ActiveWorkbook.Worksheets
       For Each oChart In sht.ChartObjects
        For Each MySeries In oChart.Chart.SeriesCollection
         Set rPatterns = Sheets("Color_Scheme").Range("A1:A18")
          
          With oChart.Chart
          For iSeries = 1 To .SeriesCollection.Count
            Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, _
                LookAt:=xlWhole)
            If Not rSeries Is Nothing Then
              .SeriesCollection(iSeries).Format.Fill.ForeColor.RGB = _
                  rSeries.Interior.Color
              .SeriesCollection(iSeries).Format.Line.ForeColor.RGB = _
                  rSeries.Interior.Color
            End If
          Next
          End With
         Next MySeries
        Next oChart
      Next sht
    End Sub
  69. Pat –
    Thanks for sharing your updated code.

  70. Hi Jon,

    Thanks for this, I have found it very useful.

    I am trying to use the code below (provided by yourself) to colour a set of 100% stacked bar chart on the active worksheet. My colours are in the range A2:A8.

    The problem is that when I run the macro it works, but only for the first series of data. What do I need to change to get it to colour the second and third series of data?

    Thanks for your help

    CODE:

    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(“A2:A8”)
    For Each chtob In ActiveSheet.ChartObjects
    With chtob.Chart.SeriesCollection(1)
    vCategories = .XValues
    For iCategory = 1 To UBound(vCategories)
    Set rCategory = rPatterns.Find(What:=vCategories(iCategory))
    .Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex
    Next
    End With
    Next
    End Sub

  71. James –

    You need to loop through all the series:

    Sub ColorByCategoryLabel()
      Dim rPatterns As Range
      Dim iCategory As Long
      Dim vCategories As Variant
      Dim rCategory As Range
      Dim chtob As ChartObject
      Dim iSrs As Long
    
      Set rPatterns = ActiveSheet.Range("A2:A8")
      For Each chtob In ActiveSheet.ChartObjects
        For iSrs = 1 To chtob.Chart.SeriesCollection.Count
          With chtob.Chart.SeriesCollection(iSrs)
            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
      Next
    End Sub
  72. Jon,

    That makes sense, thank you.

    I’ve just tried to use the macro, however it still appears to just colour the first series of data. Does the data table layout need a particular format?

    Thanks for your help

  73. James –
    Duh. I included a loop variable, but still check only series 1, because I didn’t change .SeriesCollection(1) to .SeriesCollection(iSrs). It’s now fixed in the code above.

  74. Hi John,
    Now works a chart, thanks.
    Would it be possible to colours the stacked bars with different colours? At the moment, the colours are the same for each stack/part within the respective bar.
    Can you help?
    Thanks

  75. Hi Jon,

    I wondered if you’d had any success or hints on how to create different colours within the stacked bar chart?

    Thanks

  76. James –
    To see how to separately format individual points within a series, check out the two links at the end of the article.

  77. Jon,

    I’m back again, this time with a slightly different request. Rather than change the color by series, is it possible to change the color by items in a series? For example, if I wanted to create a pie chart with 4 items and their percentages (where all 4 items belonged in one series), and I wanted the four items to come out in the color specified in a master list (akin to the “color_scheme” of the example below), what would I do?

    This wonderful code you gave me works for multiple series, but not for items within a series. Any help you could provide would be greatly appreciated.

    Sub ColorBySeriesName()
    Dim sht As Worksheet
    Dim oChart As ChartObject
    Dim MySeries As Series
    Dim rPatterns As Range
    Dim iSeries As Long
    Dim rSeries As Range
    Dim MyChart As Chart

    For Each sht In ActiveWorkbook.Worksheets
    For Each oChart In sht.ChartObjects
    For Each MySeries In oChart.Chart.SeriesCollection
    Set rPatterns = Sheets(“Color_Scheme”).Range(“A1:A18”)

    With oChart.Chart
    For iSeries = 1 To .SeriesCollection.Count
    Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, _
    LookAt:=xlWhole)
    If Not rSeries Is Nothing Then
    .SeriesCollection(iSeries).Format.Fill.ForeColor.RGB = _
    rSeries.Interior.Color
    .SeriesCollection(iSeries).Format.Line.ForeColor.RGB = _
    rSeries.Interior.Color
    End If
    Next
    End With
    Next MySeries
    Next oChart
    Next sht
    End Sub

    Here is the code I tried (it failed):

    Sub ColorBySeriesName2()
    Dim oChart As ChartObject
    Dim myseries As Series
    Dim rPatterns As Range
    Dim Listvalues As Variant
    Dim rSeries As Range
    Dim MyChart As Chart
    Dim iseries As Long
    Dim i As Integer

    For Each oChart In ActiveSheet.ChartObjects

    For Each myseries In oChart.Chart.SeriesCollection
    Set rPatterns = Sheets(“sheet1”).Range(“A1:A5”)

    With oChart.Chart
    Listvalues = myseries.Values
    For iseries = 1 To .SeriesCollection.Count
    For i = 1 To UBound(Listvalues)
    Set rSeries = rPatterns.Find(what:=.SeriesCollection(iseries).Name, lookat:=xlWhole)
    If Not rSeries Is Nothing Then
    myseries.Points(i).Interior.Color = rSeries.Interior.Color
    End If
    Next
    Next
    End With
    Next myseries
    Next oChart
    End Sub

    Thank you!
    Patrick

  78. Patrick –
    By “items in a series”, do you mean VBA Conditional Formatting of Charts by Category Label?

  79. Jon,

    I did mean category label, and that looks like exactly what I needed.

    Thanks again!
    Patrick

  80. Hello Jon,
    This thread is a lttle old – but it helped me out tremendously today. I am a beginner in the macro arena, I think this was the first successful one I have used. I have a question – I am using this because I have a series of charts in a worksheet, and wanted the chart lines to be the same color from chart to chart depending on the series name from chart to chart. The above macro worked perfect for that. My question – my charts also all change based on a users selection from a drop down list (the user can select from 15 regions in a drop down list to see data specific to their region), is there a way to make the charts automatically update to the color scheme I have defined? Right now, I have to run the macro each time a new selection is made on the drop down list. My users of this sheet will likely not have a clue how to run a macro (I barely do myself), so I wanted to see if I could have this automatically run when the file is opened, and every time a new selection is picked from the drop down list.

    Any advice is appreciated! Thank you!

    Danny

  81. Hi Jon,

    You are a God sent! Just thought I’d start with that.

    I know this thread is a couple years old but I could really use your help! So I have 0 VBA coding experience, told my boss that in the interview and was told I wouldn’t need such skills, well here I am today being asked to complete a task that I researched and can only be completed by VBA and when I ask for help the reply was “we are counting on you.” Well with that being said after countless hours of research I found this site and a solution to my troubles but I need a couple modifications to the code to make it nothing less then perfect. Please bare with me as I copy and paste a lot of codes and ask you if you can, or if it is possible to combine them to get my task done. So on this thread I found the code that seems it will work best for me. What I am looking for is a code that will have the colors on a line graph, this includes the color of the line the color of the marker fill, the marker style and the data label style, move with the brand if the brand moves. So on my spreadsheet the graph moves with the brand so for instance if Company A is number 1 this week but next week drops to spaces and moves to number 3 a new company never on the graph comes in and claims the number one spot I need all the color coding for company A to move with it and the new company to have the color I assign it. I really hope that makes sense, with that being said below you will see the code I found on this thread that works somewhat:

    Sub ColorAndMarkerBySeriesName()
      Dim rPatterns As Range
      Dim iSeries As Long
      Dim rSeries As Range
      Dim iColorIndex As Long
    
      Set rPatterns = ActiveSheet.Range("A1:A4")
      With ActiveChart
        For iSeries = 1 To .SeriesCollection.Count
          Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name)
          If Not rSeries Is Nothing Then
            iColorIndex = rSeries.Interior.ColorIndex
            With .SeriesCollection(iSeries)
              .Border.ColorIndex = iColorIndex
              .MarkerForegroundColorIndex = iColorIndex
              .MarkerBackgroundColorIndex = iColorIndex
            End With
            Select Case LCase$(rSeries.Offset(, 1).Value)
              Case "square"
                .SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleSquare
              Case "circle"
                .SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleCircle
              Case "triangle"
                .SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleTriangle
              Case "diamond"
                .SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleDiamond
            End Select
          End If
        Next
      End With
    End Sub

    In this code I need to include something you said to a participant earlier about changing the font color of the cells font to apply it to the data labels but I cannot find where I need to insert this line:

    .SeriesCollection(iSeries).DataLabels.Font.ColorIndex = _
                rSeries.Font.ColorIndex

    Also I found this on another code during my hours of research and wish to implement it if possible:

    'Loop through all charts in the active sheet
    For Each oChart In ActiveSheet.ChartObjects
     
        'Loop through all series in the target chart
       For Each MySeries In oChart.Chart.SeriesCollection

    I believe this will allow me to run the macro and it will run for every chart on the Worksheet instead of me having to click every chart and run the macro. Please correct me if I am wrong! I do not oppose running it for every chart since you can assign a short cut key but I know this is something my boss is going to ask.

    For the marker options I see inside the code that you have wrote lines to assign marker options but is it possible to apply specific marker options to specific brands? For instance Company A is always Diamonds Company B is always a square so on and so forth?

    I know this is a lot I just threw at you on an old thread so I am unsure if you will even see this but if you do and you have the time to supply me and answer, well then you will help me earn some real cool points with my boss, until they ask me to do it again that is!

    Thank you for you Blog Jon

  82. Wes –

    I think this has what you need. Not tested…

    Sub ColorAndMarkerBySeriesName()
      Dim rPatterns As Range
      Dim iSeries As Long
      Dim rSeries As Range
      Dim iColorIndex As Long
      Dim oChart As ChartObject
    
      Set rPatterns = ActiveSheet.Range("A1:A4")
      For Each oChart In ActiveSheet.ChartObjects
        With oChart.Chart
          For iSeries = 1 To .SeriesCollection.Count
            Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name)
            If Not rSeries Is Nothing Then
              iColorIndex = rSeries.Interior.ColorIndex
              With .SeriesCollection(iSeries)
                .Border.ColorIndex = iColorIndex
                .MarkerForegroundColorIndex = iColorIndex
                .MarkerBackgroundColorIndex = iColorIndex
                .DataLabels.Font.ColorIndex = rSeries.Font.ColorIndex
              End With
              Select Case LCase$(rSeries.Offset(, 1).Value)
                Case "square"
                  .SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleSquare
                Case "circle"
                  .SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleCircle
                Case "triangle"
                  .SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleTriangle
                Case "diamond"
                  .SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleDiamond
              End Select
            End If
          Next
        End With
      Next
    End Sub
  83. Hi Jon,

    Here is an update I wrote this line of code

    .DataLabels.Font.ColorIndex = rSeries.Font.ColorIndex

    Now I am trying find a way to bold the data label, slowly but surely.

  84. Jon,

    Update 2.

    looks like I got the data labels to be bold, now if I figure out the Marker Styles I will be set! Below is my code so far (well your code with 2 lines I just so happened to come up with)

    Sub ColorAndMarkerBySeriesName()
      Dim rPatterns As Range
      Dim iSeries As Long
      Dim rSeries As Range
      Dim iColorIndex As Long
    
      Set rPatterns = ActiveSheet.Range("A1:A5")
      With ActiveChart
        For iSeries = 1 To .SeriesCollection.Count
          Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name)
          If Not rSeries Is Nothing Then
            iColorIndex = rSeries.Interior.ColorIndex
            With .SeriesCollection(iSeries)
              .Border.ColorIndex = iColorIndex
              .MarkerForegroundColorIndex = iColorIndex
              .MarkerBackgroundColorIndex = iColorIndex
              .DataLabels.Font.ColorIndex = rSeries.Font.ColorIndex
              .DataLabels.Font.Bold = rSeries.Font.Bold
              
            End With
            Select Case LCase$(rSeries.Offset(, 1).Value)
              Case "square"
                .SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleSquare
              Case "circle"
                .SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleCircle
              Case "triangle"
                .SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleTriangle
              Case "diamond"
                .SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleDiamond
            End Select
          End If
        Next
      End With
    End Sub
  85. If all of the labels are going to be bold, all you need is:
    .DataLabels.Font.Bold = True

    The markers are defined by a label in the cell to the right of the cell with the matching color, where the label is “square”, “circle”, etc.

  86. Hi Jon,

    I never knew you responded because I didn’t get an email update, not sure if I was supposed to or not though I just kind of assumed.

    But anyway It Works! Thank you Jon I really appreciate your help. As challenging as it was think I may take some steps at learning VBA in the near future.

    Thanks again,

    Wes

  87. Jon,

    It me again unfortunately. It worked perfect in my test document now that I am trying to take it live to my actual file every thing is working but the data label codes. I am getting an error message that reads

    “Run-time error ‘-214746259 (80004005):

    Unable to get the Count property of the DataLabels class.”

    here are the codes that are highlighted

    .DataLabels.Font.ColorIndex = rSeries.Font.ColorIndex
    .DataLabels.Font.Bold = rSeries.Font.Bold

    Even the .DataLabels.Font.Bold = True code isn’t working.
    When I delete these everything else is fine.

    Not sure what I did wrong but can you help one more time.

  88. Are there data labels?

  89. Just thought of that myself.

    Some lines have data labels some do not. And it is preferable to keep it that way. Is that possible?

  90. So you need something like this:

    On Error Resume Next
      .DataLabels.Font.ColorIndex = rSeries.Font.ColorIndex
      .DataLabels.Font.Bold = rSeries.Font.Bold
    On Error Goto 0
  91. Jon,

    One last thing, is there a reason that the VBA code pulls the color either lighter or dark then it would usually show if I selected the color? for Instance Purple Accent 4, Lighter 80% looks kind of gray when pulled. not a huge deal I’m ready to look past it but I just thought I’d ask.

  92. The code in this tutorial all uses ColorIndex, which has nothing to do with the color picker introduced in Excel 2007.

    I’d suggest you experiment with the macro recorder while you format some dummy chart series, and see what the effect of .Brightness is on the resulting color. Ignore .TintAndShade, because that’s broken.

  93. Jon,

    So after 4 days of research and figuring out how to write this code since the colors will not come out like the selected colors on the color picker in excel the team doesn’t accept the report! What a waste! Well I would like to paste the final code so maybe it can help someone else in the future trying to do something similar.

    Again Jon thank you for all your help!

    Sub ColorAndMarkerBySeriesName()
      Dim rPatterns As Range
      Dim iSeries As Long
      Dim rSeries As Range
      Dim iColorIndex As Long
      Dim oChart As ChartObject
    
      Set rPatterns = ActiveSheet.Range("A14:A31")
      For Each oChart In ActiveSheet.ChartObjects
        With oChart.Chart
          For iSeries = 1 To .SeriesCollection.Count
            Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name)
            If Not rSeries Is Nothing Then
              iColorIndex = rSeries.Interior.ColorIndex
              With .SeriesCollection(iSeries)
                .Border.ColorIndex = iColorIndex
                .MarkerForegroundColorIndex = iColorIndex
                .MarkerBackgroundColorIndex = iColorIndex
              On Error Resume Next
                .DataLabels.Font.ColorIndex = rSeries.Font.ColorIndex
                .DataLabels.Font.Bold = True
              On Error GoTo 0
              End With
              Select Case LCase$(rSeries.Offset(, 1).Value)
                Case "square"
                  .SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleSquare
                Case "circle"
                  .SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleCircle
                Case "triangle"
                  .SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleTriangle
                Case "diamond"
                  .SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleDiamond
              End Select
            End If
          Next
        End With
      Next
    End Sub
  94. Sub ColorBySeriesName()
      Dim rPatterns As Range
      Dim iSeries As Long
      Dim rSeries As Range
    
      Set rPatterns = ActiveSheet.Range("A1:A4")
      ' -------------> Is it possible to have a userrange function with a input box where you then manually select the range? 
      ' This would help if there are multiple charts. I have tried to code this but havent had any luck. Would appreciate the help thanks.
      With ActiveChart
        For iSeries = 1 To .SeriesCollection.Count
          Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, _
              LookAt:=xlWhole)
          If Not rSeries Is Nothing Then
            .SeriesCollection(iSeries).Format.Fill.ForeColor.RGB = _
                rSeries.Interior.Color
          End If
        Next
      End With
    End Sub
  95. Replace

    Set rPatterns = ActiveSheet.Range("A1:A4")

    with

    On Error Resume Next
    Set rPatterns = Application.InputBox("Select a range containing formatted cells and series names",,,,,,,8)
    If rPatterns Is Nothing Then Exit Sub
  96. Hi John,

    Great, thank you, this is exactly what I need, except I am trying to change the colours of a simple bar chart that has been created in Powerpoint (2011). How do I select an active chart that is in the pptx while looking at the data that is in excel?

    Thanks again,
    Mark

  97. Hi Mark –

    I’m not ignoring your comment, it’s a very good point.

    I’m preparing software to work together with Excel and PowerPoint, and I will be preparing some tutorials on related topics. In addition I will catch up tutorials like this one with extensions to PowerPoint-based charts.

  98. Terry Ginzburg says:

    Hi John,

    This is great information, so thank you. I’ve got a set of charts with related data (e.g. job roles), and am using this to control color so the color for each data element is consistent. It’s working on my column charts perfectly, but on the line charts (using the code you provided above) it runs and changes the colors, but the lines themselves end up a different color than the one in my range. It’s getting the right color, but I think the problem is that the .Border code is setting the outline of the line, not the fill. When I use go to the chart myself and set the color manually, it’s the Outline color that is actually filling the line (not sure why that is, but that’s the way it seems to work).

    My problem is that I can’t seem to find the right code to set the ‘Outline’ color on the lines. Can you help?

    Here is my code, I think I just need to add a line below .Border… to address the outline of the line.

    Sub ColorBySeriesName_LineGraph()
      Dim rPatterns As Range
      Dim iSeries As Long
      Dim rSeries As Range
      Dim iColorIndex As Long
    
      Set rPatterns = Sheets("Color Map for VB").Range("A1:A39")
    
      With ActiveChart
        For iSeries = 1 To .SeriesCollection.Count
          Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name)
          If Not rSeries Is Nothing Then
            iColorIndex = rSeries.Interior.ColorIndex
            With .SeriesCollection(iSeries)
              .Border.ColorIndex = iColorIndex
              .MarkerForegroundColorIndex = iColorIndex
              .MarkerBackgroundColorIndex = iColorIndex
            End With
          End If
        Next
      End With
    End Sub

    Thank you so much!
    Terry

  99. Terry –

    Try this first. The old ColorIndex approach is based on the color system used Excel 2003 and earlier, and is not reliable in more recent versions. Change .ColorIndex to .Color and see if that fixes the issue.

  100. Terry Ginzburg says:

    Thank you, Jon, worked like a charm!

  101. Hi Jon,

    I tried the same on a graph created on a Pivot table and consequently want to change the colours or my dash board too. Is it possible that that I can define my chart in the code- like chart 1 or so and then use the code rather than having to click on a macro button each time since a dashboard would have multiple graphs of the same type?

    Thanks!!
    Deepthi

  102. Hello Jon,
    This VBA code is very useful for me. Just wanted to let you know that I do not know much in this field ..
    Now I had to create a chart with a variable data range (a colleague helped with the code – written below) and I don’t know how to adapt your formatting solution here.

    G1: =COUNTIF((G5:G120);">0")
    Sub data_chart()
        anz = 4 + Range("G1").Value
        ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveChart.PlotArea.Select
        range_ = "F4:G" & anz
        ActiveChart.SetSourceData Source:=Range(range_), PlotBy:=xlRows
        Range("I1").Select
    End Sub

    Thank you,
    Cezara

  103. Right after SetSourceData, you might incorporate something like this code, taken from the last example in the post:

    Set rSeries = rPatterns.Find(What:=.SeriesCollection(1).Name, _
        LookAt:=xlWhole)
    If Not rSeries Is Nothing Then
      .SeriesCollection(1).Format.Fill.ForeColor.RGB = _
          rSeries.Interior.Color
    End If
  104. Thank you, I will try this.

  105. Christian Bahnsen says:

    Well done! I used your code almost verbatim to come up with a solution for a question I’d posted on the Excel IT Pro forum: https://social.technet.microsoft.com/Forums/office/en-US/7eb70726-c5de-48e8-8ba7-bb1edf94867b/excel-2010-how-can-i-specify-the-color-to-use-for-each-series-in-a-stacked-bar-chart?forum=excel

  106. I would like to use this code to only change the colours of one series which I naned “status” when creating the series. I cannot get it to work. I get the error:
    Object variable or width variable not set. Please help.

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

    Worksheets(“sheet1”).ChartObjects(“chTasks”).Select
    Set rPatterns = ActiveWorkbook.Worksheets(“sheet1”).Range(“L1:L6”)

    Worksheets(“sheet1”).ChartObjects(“chTasks”).Select
    With ActiveChart.SeriesCollection(“status”)
    vCategories = .XValues

    For iCategory = 1 To UBound(vCategories)
    ‘MsgBox (“I category ” & iCategory)
    Set rCategory = rPatterns.Find(What:=vCategories(iCategory))

    Error here- ActiveChart.SeriesCollection(“status”).Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex
    Next
    End With

    End Sub

  107. Martina –

    This is more closely related to my other post, VBA Conditional Formatting of Charts by Category Label. In addition, unless you’re still using Excel 2003 or earlier, you should be using the syntax with Color not ColorIndex.

    But in any case, the code doesn’t fail if there is a sheet named “sheet1”, there is a series named “status”, and the category (X value) for each point is contained withing the range rPatterns.

Trackbacks

  1. Linda Sabeh says:

    Linda Sabeh

    VBA Conditional Formatting of Charts by Series Name – Peltier Tech Blog

Speak Your Mind

*

Peltier Tech Charts for Excel 3.0