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.

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

  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
    10. 'Loop through all charts in the active sheet
    11. For Each oChart In ActiveSheet.ChartObjects
    13.     'Loop through all series in the target chart
    14.   For Each MySeries In oChart.Chart.SeriesCollection
    16.        NumberofDataPoints = MySeries.Points.Count
    18.        For iPoint = 1 To NumberofDataPoints
    20.            'Get Source Data Range for the target series
    21.          FormulaSplit = Split(MySeries.Formula, ",")
    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
    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
    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
    41.         Next
    42.     Next MySeries
    43. Next oChart
    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

    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?

  19. Alex

    This is one hell of a helping code!

    But I have one further question. This code changes the colour of each Chart in Excel. How do I change it if I want to change the colour of just 6 charts?

    Can somebody help me with this issue?

  20. Kasper

    Nice Macro,

    Doesn’t work if you have a graph with multiple series with x-values and separate y-values though. You will get every line/symbols in the color of the first cell of the x values.

    A nice way to solve this would be to refer to the Series Name cell, and give this cell the desired color for each separate series.

    I know nothing of vba though.. have been looking for a way to do this, with no succes.

    Do you have any ideas how to do this?


  21. George

    Hello. Please suggest how to apply this function only to one plot om the sheet. I have many of them on the same sheet but whant to be able to apply to only one of them.


  22. Lewis Francis


    I am not a whiz on code, and I am struggling to replicate the above in google sheets
    Is it possible?

    Is there any way of using Google sheets script editor to do this, just like using VBA in Excel?




  23. Tim Johnsen

    omg life saver thank you so much! I had to delete the last line that set the fill color so that I could have it as a line graph and not a line with blobs graph, easy fix! saved so much time =D

  24. Dan

    Chris, Thanks #17 works really well.
    But to remove conditional formatting I had to copy into word and paste back into excel.

  25. Laura

    years after you post this, it is still so useful! exactly what I was looking for and you made it so easy just copy and paste. Thank you.

  26. LinaY

    Brilliant! You’ve saved my life/career… I’ve been searching for the correct VBA code for what I need for two days now… and finally found yours and it works like magic!

    Thanks a million 🙂

  27. FirstHarrison

    I see you don’t monetize your site, don’t waste your
    traffic, you can earn additional bucks every month because you’ve got hi quality content.

    If you want to know how to make extra money, search for: Boorfe’s
    tips best adsense alternative

  28. Cesar

    Wonderful chart.
    Is there a way to color a stacked chart. Each point can be their own color.
    When I record the macro, coloring points on the chart i have this “ActiveChart.FullSeriesCollection(1).Points(2).Select”. Just an example for what I´d like to have.
    I don´t know how to add this.

Leave a Reply

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