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.

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

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>