Automatically Set Chart Series Colors to Match Source Cell Colors

While working on a dashboarding project here, I accidently developed a charting macro that will automatically set the series colors in a chart to match their source cell’s colors.

.

I’m not sure how useful this actually could be, but it’s charming enough to share with my 12 fans.

.

Let’s say I created this chart – and I want to apply specific coloring to each series without having to painstakingly change all the color options in each series.

.

All I have to do is color the source cells that feed my chart series….

.

…then I run this macro…

  1. Sub CellColorsToChart()
  2. Dim oChart As ChartObject
  3. Dim MySeries As Series
  4. Dim FormulaSplit As Variant
  5. Dim SourceRange As Range
  6. Dim SourceRangeColor As Long
  7.  
  8. 'Loop through all charts in the active sheet
  9. For Each oChart In ActiveSheet.ChartObjects
  10.  
  11.     'Loop through all series in the target chart
  12.    For Each MySeries In oChart.Chart.SeriesCollection
  13.        
  14.         'Get Source Data Range for the target series
  15.        FormulaSplit = Split(MySeries.Formula, ",")
  16.        
  17.         'Capture the first cell in the source range then trap the color
  18.        Set SourceRange = Range(FormulaSplit(2)).Item(1)
  19.         SourceRangeColor = SourceRange.Interior.Color
  20.  
  21.         On Error Resume Next
  22.         'Coloring for Excel 2003
  23.        MySeries.Interior.Color = SourceRangeColor
  24.         MySeries.Border.Color = SourceRangeColor
  25.         MySeries.MarkerBackgroundColorIndex = SourceRangeColor
  26.         MySeries.MarkerForegroundColorIndex = SourceRangeColor
  27.        
  28.         'Coloring for Excel 2007 and 2010
  29.        MySeries.MarkerBackgroundColor = SourceRangeColor
  30.         MySeries.MarkerForegroundColor = SourceRangeColor
  31.         MySeries.Format.Line.ForeColor.RGB = SourceRangeColor
  32.         MySeries.Format.Line.BackColor.RGB = SourceRangeColor
  33.         MySeries.Format.Fill.ForeColor.RGB = SourceRangeColor
  34.    
  35.     Next MySeries
  36. Next oChart
  37.  
  38. End Sub

.

…and Zoomo! The chart will automatically adopt the colors in the source range.

.

It works for column charts too.

.

Notes:

1. To use the macro, simply copy the code in this post and paste it into a standard module.

2. As the macro is written, the chart can only adopt the color of the first cell in the series range. So you technically only have to color the first cell in source data for each series.

3. Remember this macro colors the entire chart series, not individual data points – so you couldn’t use this on a Pie Chart.

4. In my limited testing, I’ve confirmed that this macro works in Excel 2003, 2007, 2010.

5. And before you dashboarding nerds ping me, I know that gaudy colors like Yellow, Red, and Orange are bad – I used them for demonstration purposes only.

You can download a demo here

.

Hopefully, this is useful to somebody out there.

73 thoughts on “Automatically Set Chart Series Colors to Match Source Cell Colors

  1. Marvin

    Is it possible to color code the table portion of a bar graph. I am trying to accomplish having ie. Jan-Jun one color then Jul-Dec another color. This would be the table that is incorporated within the graph. Thanks.

  2. AJ

    This is really nice. Worked for me in Excel 2013. Does not appear to work for charts that comprise their own sheet, but that is easy to work around.

    My boss spent 2 hours coloring a chart… I found this and did it in 4 minutes and made him pretty mad 😉

  3. Kevin

    Thanks for this – most useful :)
    It does reset the chart type to “lines with blobs” – but that’s easy to undo.
    A related thing – mentioned often in the comments, is a means to link to the conditional formatting colours. I think what is really wanted here is a way to define cell colour based on numbers, so you can get a rainbow or a 2-colour fade.
    Can we have a macro that takes three cells with RGB numbers and colours the 4th cell in said column to the RGB value specified? this can then be the title row in the macro offered here.
    BTW – I seem to need to colour both the title row and the first data, to be sure the graph picks-up the right colours.

    good work, many thanks !

  4. Christophe

    Many thanks sharing your macro!

    However, when i apply it to a table with conditional formatting, everything is blank. Is it possible to extend it so that it takes into account the colour of the cells due to conditional formatting?

  5. Christophe

    Actually, doing the conditional formating by vba is totally feasible. But the macro takes the colour of the first cell of each serie and apply it to the whole serie. How would you modify the code so that it takes into account the colour of each individual cell of each serie?

    Many thanks in advance!

  6. Iris Lopes

    Hi. This macro SAVED ME! I am doing color analisys on a Thesys about colors on Comics. Well, I needed to have the colours ina chart, according to the percentage they appeared in the pages – and voilá your macro saved my chart!!!
    Thank you!

  7. Graham

    This is really good. Ive covered VBA along time ago and now im in need of using it am and very rusty to say the least.

    I am after a similar program in theory, Ive been trying to create a VBA that colors the corresponding cells in different worksheets when they are coloured manually.

    Any help/advice would be much appreciated

    Regards

    Graham King

  8. Nick

    I cant code, and was looking for something to save me time, I downloaded your demo and have modified it for what I needed and I love it!
    Thanks

    Nick

  9. Eidjaz Parwaz

    To change the color, which is based on conditional formatting, change the following in your vba code:

    SourceRangeColor = SourceRange.Interior.Color

    to

    SourceRangeColor = SourceRange.DisplayFormat.Interior.Color

    Good luck,
    Eidjaz

  10. joe

    So you technically only have to color the first cell in source data for each series.

    <== can you explain further? Mine detected the color in the first cell, but not the rest. Now all series have the same color..

    thanks

  11. Jay

    How can I get the labels to be the same colors as the text in the cell it’s coming from? The colors are using conditional formatting so if it’s yellow background it would be Black text, if it’s red background it should be white text.

  12. tim

    So I have a massive sheet and have tried to use this. I have a large cell with a lot of ranges how can I get it to span the entire sheet.

  13. Chris

    I have the same issue as tim and joe. This would be extra powerful if we could color each data point in the series… I’m trying to figure out the syntax for the extra “For” loop required… will revert if i do but could use help

  14. Chris

    Got it… I’m sure there’s more elegant ways but it seems to work:

    1. Sub CellColorsToChart()
    2. Dim oChart As ChartObject
    3. Dim MySeries As Series
    4. Dim FormulaSplit As Variant
    5. Dim SourceRange As Range
    6. Dim SourceRangeColor As Long
    7. Dim NumberofDataPoints As Long
    8. Dim iPoint As Long
    9.  
    10. 'Loop through all charts in the active sheet
    11. For Each oChart In ActiveSheet.ChartObjects
    12.  
    13.     'Loop through all series in the target chart
    14.   For Each MySeries In oChart.Chart.SeriesCollection
    15.        
    16.        NumberofDataPoints = MySeries.Points.Count
    17.        
    18.        For iPoint = 1 To NumberofDataPoints
    19.        
    20.            'Get Source Data Range for the target series
    21.          FormulaSplit = Split(MySeries.Formula, ",")
    22.          
    23.            'Capture the first cell in the source range then trap the color
    24.          Set SourceRange = Range(FormulaSplit(2)).Item(iPoint)
    25.            SourceRangeColor = SourceRange.DisplayFormat.Interior.Color
    26.    
    27.            On Error Resume Next
    28.            'Coloring for Excel 2003
    29.          'MySeries.Interior.Color = SourceRangeColor
    30.         '  MySeries.Border.Color = SourceRangeColor
    31.        '   MySeries.MarkerBackgroundColorIndex = SourceRangeColor
    32.        '   MySeries.MarkerForegroundColorIndex = SourceRangeColor
    33.          
    34.            'Coloring for Excel 2007 and 2010
    35.          MySeries.Points(iPoint).MarkerBackgroundColor = SourceRangeColor
    36.            MySeries.Points(iPoint).MarkerForegroundColor = SourceRangeColor
    37.            MySeries.Points(iPoint).Format.Line.ForeColor.RGB = SourceRangeColor
    38.            MySeries.Points(iPoint).Format.Line.BackColor.RGB = SourceRangeColor
    39.            MySeries.Points(iPoint).Format.Fill.ForeColor.RGB = SourceRangeColor
    40.        
    41.         Next
    42.     Next MySeries
    43. Next oChart
    44.  
    45. End Sub

    Thanks again to datapig OP for the solution!

  15. C. Brechen

    Can you use this macro or a revised macro to also set the format of the marker options in the cell. For example, I want x row to be red with a square and y row to be yellow with a circle?

  16. Mike

    This is awesome and works on Excel 2013. I know nothing about VBA and it worked great on a scatter plot. Two things for other newbies.

    1. There is a circle that is getting inserted before the word Chart…you will need to delete that in several places
    2. If your worksheet has a comma in the name, it will not work. OP, is there any way to fix this?

  17. PAC

    Hello
    I am a very begginner with macro…
    I have data organised by categories and years and a total on all years
    In my spread sheet I have both a cumulated area chart (to show the distribution of categories accross years) and a pie chart to show the total distribution.
    The orginal macro works fine for the cumulated area chart (but colors the pie chart with only 1 color which is logic as there is only 1 category), and the 2nd macro from chris, then works fine on the pie chart and has no effect on the cumulated area chart.
    Therefore by running the 2 macro (1st then 2nd) I have the result I want but would it be possible to have only macro that would do it at the same time?
    Many thanks

  18. Andrew Sasse

    Hi, this is really useful but I am trying to do this specifically on pie charts. With this macro the color of the first cell colors the whole chart. Can this be changed?

Leave a Reply

Your email address will not be published. Required fields are marked *