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…
- Sub CellColorsToChart()
- Dim oChart As ChartObject
- Dim MySeries As Series
- Dim FormulaSplit As Variant
- Dim SourceRange As Range
- Dim SourceRangeColor As Long
- '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
- 'Get Source Data Range for the target series
- FormulaSplit = Split(MySeries.Formula, ",")
- 'Capture the first cell in the source range then trap the color
- Set SourceRange = Range(FormulaSplit(2)).Item(1)
- SourceRangeColor = SourceRange.Interior.Color
- On Error Resume Next
- 'Coloring for Excel 2003
- MySeries.Interior.Color = SourceRangeColor
- MySeries.Border.Color = SourceRangeColor
- MySeries.MarkerBackgroundColorIndex = SourceRangeColor
- MySeries.MarkerForegroundColorIndex = SourceRangeColor
- 'Coloring for Excel 2007 and 2010
- MySeries.MarkerBackgroundColor = SourceRangeColor
- MySeries.MarkerForegroundColor = SourceRangeColor
- MySeries.Format.Line.ForeColor.RGB = SourceRangeColor
- MySeries.Format.Line.BackColor.RGB = SourceRangeColor
- MySeries.Format.Fill.ForeColor.RGB = SourceRangeColor
- Next MySeries
- Next oChart
- 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.
Hopefully, this is useful to somebody out there.