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
  8. 'Loop through all charts in the active sheet
  9. For Each oChart In ActiveSheet.ChartObjects
  11.     'Loop through all series in the target chart
  12.    For Each MySeries In oChart.Chart.SeriesCollection
  14.         'Get Source Data Range for the target series
  15.        FormulaSplit = Split(MySeries.Formula, ",")
  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
  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
  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
  35.     Next MySeries
  36. Next oChart
  38. End Sub


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


It works for column charts too.



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.

63 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


    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!


  9. Eidjaz Parwaz

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

    SourceRangeColor = SourceRange.Interior.Color


    SourceRangeColor = SourceRange.DisplayFormat.Interior.Color

    Good luck,

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


  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.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>