Set Chart Series Colors to Match Category Cell Colors VBA

Question

I need VBA macro that will match my categories background colors with line chart series colors. Right now I'm not using best way as I'm applying following code

It sets chart series colors same as source cell colors. (example on pic)

BUT I want this macro to take colors from categories's cells (2009, 2010, 2011 representatively) instead of source cells.

I cannot find the way to do it simply and straight forward. I'm macro-setting background colors for source cells to match categories colors and then I'm putting white color on top of source cells with conditional formatting. So only categories are colorful and source cells are white.

Was wondering if there's better way of doing that. (final result on pic, categories's names matching series colors)

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

its not that clear what you are after. Can you post a sample screenshot of what you want and also be more specific as to what you're code is doing (or not doing)? — Scott Holtzman

Scott Holtzman Scott Holtzman · Accepted Answer · 2016-06-06T20:21:40

Assuming that I understand exactly what you are asking, you were very close to being there. I think the issue in your code was how you were splitting the series formula to get the label color.

I turned this chart, with column headers colored as such:

into the chart below with this code:

Sub SetColors()

Dim oChart As ChartObject
Dim MySeries As Series

For Each oChart In ActiveSheet.ChartObjects

    For Each MySeries In oChart.Chart.SeriesCollection

        Dim sFormula As String
        sFormula = Split(MySeries.Formula, ",")(0) 'this returns the =SERIES(Sheet!RC part of the formula, the first argument is the series label
        sFormula = Split(sFormula, "(")(1) 'this removes the =SERIES(  leaving only the column label range (Sheet!RC)

        Dim lSourceColor As Long
        lSourceColor = Range(sFormula).Interior.Color

        With MySeries
            .Interior.Color = lSourceColor
            .Border.Color = lSourceColor
            '.MarkerBackgroundColorIndex = lSourceColor
            '.MarkerForegroundColorIndex = lSourceColor
            .MarkerBackgroundColor = lSourceColor
            .MarkerForegroundColor = lSourceColor
            With .Format.Line
                .ForeColor.RGB = lSourceColor
                .BackColor.RGB = lSourceColor
            End With
            .Format.Fill.ForeColor.RGB = lSourceColor
        End With

    Next

Next

End Sub

Set Chart Series Colors to Match Category Cell Colors VBA

1 Answers