Update chart series colours

[Rudi]

Hi,

The code below successfully updates the chart series colours for all charts in a workbook.
However, I need it to work on line charts (which it does), column charts, as well as line/column mix.

How do I update the code to do this?
TX.

Code: Select all

Sub RecolorCharts()

    Dim objChart As ChartObject
    Dim shtTemp As Worksheet
    Dim lngIndex As Long
    Dim objSeries As Series
    Dim lngColors(1 To 7) As Long
    
    lngColors(1) = RGB(34, 70, 107)
    lngColors(2) = RGB(227, 35, 51)
    lngColors(3) = RGB(126, 152, 52)
    lngColors(4) = RGB(239, 184, 25)
    lngColors(5) = RGB(75, 92, 105)
    lngColors(6) = RGB(0, 84, 166)
    lngColors(7) = RGB(0, 0, 0)
    
    For Each shtTemp In ActiveWorkbook.Worksheets
        For Each objChart In shtTemp.ChartObjects
            lngIndex = 0
            For Each objSeries In objChart.Chart.SeriesCollection
                lngIndex = lngIndex + 1
                objSeries.Format.Line.ForeColor.RGB = lngColors(lngIndex)
                If objSeries.MarkerStyle <> xlMarkerStyleNone Then
                    objSeries.MarkerForegroundColor = lngColors(lngIndex)
                    objSeries.MarkerBackgroundColor = lngColors(lngIndex)
                End If
            Next
        Next
    Next
End Sub

[HansV]

What do you want to happen for column charts? Should the line colour AND fill colour of the columns be set, or only the fill colour?

[Rudi]

What do you want to happen for column charts? Should the line colour AND fill colour of the columns be set, or only the fill colour?

Oh..TX.
Line and fill colour please.
TIA

[HansV]

Try this version. It should work (I hope) for line charts, XY scatter charts, radar charts, bar charts, column charts and area charts (all in 2D).

Code: Select all

Sub RecolorCharts()
    Dim objChart As ChartObject
    Dim shtTemp As Worksheet
    Dim lngIndex As Long
    Dim objSeries As Series
    Dim lngColors(1 To 7) As Long

    lngColors(1) = RGB(34, 70, 107)
    lngColors(2) = RGB(227, 35, 51)
    lngColors(3) = RGB(126, 152, 52)
    lngColors(4) = RGB(239, 184, 25)
    lngColors(5) = RGB(75, 92, 105)
    lngColors(6) = RGB(0, 84, 166)
    lngColors(7) = RGB(0, 0, 0)

    For Each shtTemp In ActiveWorkbook.Worksheets
        For Each objChart In shtTemp.ChartObjects
            lngIndex = 0
            For Each objSeries In objChart.Chart.SeriesCollection
                lngIndex = lngIndex + 1
                objSeries.Format.Line.ForeColor.RGB = lngColors(lngIndex)
                Select Case objSeries.ChartType
                    Case xlLineMarkers, xlLineMarkersStacked, xlLineMarkersStacked100, _
                            xlRadarMarkers, _
                            xlXYScatter, xlXYScatterLines, xlXYScatterSmooth
                        objSeries.MarkerForegroundColor = lngColors(lngIndex)
                        objSeries.MarkerBackgroundColor = lngColors(lngIndex)
                    Case xlArea, xlAreaStacked, xlAreaStacked100, _
                            xlBarClustered, xlBarStacked, xlBarStacked100, _
                            xlColumnClustered, xlColumnStacked, xlColumnStacked100
                        objSeries.Format.Fill.ForeColor.RGB = lngColors(lngIndex)
                        objSeries.Format.Fill.BackColor.RGB = lngColors(lngIndex)
                End Select
            Next objSeries
        Next objChart
    Next shtTemp
End Sub

[Rudi]

Try this version. It should work (I hope) for line charts, XY scatter charts, radar charts, bar charts, column charts and area charts (all in 2D).

Thanks Hans.
I'll test it first thing tomorrow morning from my side.

Much appreciated.
Cheers

[Rudi]

Hans, on initial test with a variety of charts (line, bar and column), it is working sweetly.
Tx for the code modification.

Cheers

[Rudi]

TX to Hans's help, here is the final code including and IF statement to avoid a debug if the chart has more than 8 series'...

Code: Select all

Sub RecolourCharts()
    Dim objChart As ChartObject
    Dim shtTemp As Worksheet
    Dim lngIndex As Long
    Dim objSeries As Series
    Dim lngColors(1 To 8) As Long

    lngColors(1) = RGB(34, 70, 107)
    lngColors(2) = RGB(227, 35, 51)
    lngColors(3) = RGB(126, 152, 52)
    lngColors(4) = RGB(239, 184, 25)
    lngColors(5) = RGB(75, 92, 105)
    lngColors(6) = RGB(0, 84, 166)
    lngColors(7) = RGB(0, 0, 0)
    lngColors(8) = RGB(0, 157, 224)

    For Each shtTemp In ActiveWorkbook.Worksheets
        For Each objChart In shtTemp.ChartObjects
            lngIndex = 0
            For Each objSeries In objChart.Chart.SeriesCollection
                lngIndex = lngIndex + 1
                If lngIndex <= 8 Then
                objSeries.Format.Line.ForeColor.RGB = lngColors(lngIndex)
                Select Case objSeries.ChartType
                    Case xlLineMarkers, xlLineMarkersStacked, xlLineMarkersStacked100, _
                            xlRadarMarkers, _
                            xlXYScatter, xlXYScatterLines, xlXYScatterSmooth
                        objSeries.MarkerForegroundColor = lngColors(lngIndex)
                        objSeries.MarkerBackgroundColor = lngColors(lngIndex)
                    Case xlArea, xlAreaStacked, xlAreaStacked100, _
                            xlBarClustered, xlBarStacked, xlBarStacked100, _
                            xlColumnClustered, xlColumnStacked, xlColumnStacked100
                        objSeries.Format.Fill.ForeColor.RGB = lngColors(lngIndex)
                        objSeries.Format.Fill.BackColor.RGB = lngColors(lngIndex)
                End Select
                End If
            Next objSeries
        Next objChart
    Next shtTemp
    MsgBox "All charts have been formatted to the updated colour template." & vbNewLine & _
    "Note: The updated template provides 8 colours, so only the first 8 series' are recoloured!", _
    vbInformation
End Sub

[HansV]

Another option would be to reuse the colours if there are more than 8 chart series: series 9 would use colour 1, series 10 would use colour 2 etc.:

Code: Select all

Sub RecolourCharts()
    Dim objChart As ChartObject
    Dim shtTemp As Worksheet
    Dim lngIndex As Long
    Dim objSeries As Series
    Dim lngColors(1 To 8) As Long
    Dim lngColor As Long

    lngColors(1) = RGB(34, 70, 107)
    lngColors(2) = RGB(227, 35, 51)
    lngColors(3) = RGB(126, 152, 52)
    lngColors(4) = RGB(239, 184, 25)
    lngColors(5) = RGB(75, 92, 105)
    lngColors(6) = RGB(0, 84, 166)
    lngColors(7) = RGB(0, 0, 0)
    lngColors(8) = RGB(0, 157, 224)

    For Each shtTemp In ActiveWorkbook.Worksheets
        For Each objChart In shtTemp.ChartObjects
            lngIndex = 0
            For Each objSeries In objChart.Chart.SeriesCollection
                lngIndex = lngIndex Mod 8 + 1
                objSeries.Format.Line.ForeColor.RGB = lngColors(lngIndex)
                Select Case objSeries.ChartType
                    Case xlLineMarkers, xlLineMarkersStacked, xlLineMarkersStacked100, _
                            xlRadarMarkers, _
                            xlXYScatter, xlXYScatterLines, xlXYScatterSmooth
                        objSeries.MarkerForegroundColor = lngColors(lngIndex)
                        objSeries.MarkerBackgroundColor = lngColors(lngIndex)
                    Case xlArea, xlAreaStacked, xlAreaStacked100, _
                            xlBarClustered, xlBarStacked, xlBarStacked100, _
                            xlColumnClustered, xlColumnStacked, xlColumnStacked100
                        objSeries.Format.Fill.ForeColor.RGB = lngColors(lngIndex)
                        objSeries.Format.Fill.BackColor.RGB = lngColors(lngIndex)
                End Select
            Next objSeries
        Next objChart
    Next shtTemp
    MsgBox "All charts have been formatted to the updated colour template.", _
        vbInformation
End Sub

[Rudi]

TX for the code...

One question.
The for each loop works on all the Worksheets in the workbook, but skips Chart sheets. How do I get the for each loop to also work on chart sheets? When I change the variable to Sheet (instead of Worksheet)...it debugs..

TX

[HansV]

You have to handle embedded charts and chart sheets separately:

Code: Select all

Sub RecolourCharts()
    Dim objChart As ChartObject
    Dim shtTemp As Worksheet
    Dim chtTemp As Chart
    Dim lngIndex As Long
    Dim objSeries As Series
    Dim lngColors(1 To 8) As Long
    Dim lngColor As Long

    lngColors(1) = RGB(34, 70, 107)
    lngColors(2) = RGB(227, 35, 51)
    lngColors(3) = RGB(126, 152, 52)
    lngColors(4) = RGB(239, 184, 25)
    lngColors(5) = RGB(75, 92, 105)
    lngColors(6) = RGB(0, 84, 166)
    lngColors(7) = RGB(0, 0, 0)
    lngColors(8) = RGB(0, 157, 224)

    ' Process embedded charts on worksheets
    For Each shtTemp In ActiveWorkbook.Worksheets
        For Each objChart In shtTemp.ChartObjects
            lngIndex = 0
            For Each objSeries In objChart.Chart.SeriesCollection
                lngIndex = lngIndex Mod 8 + 1
                objSeries.Format.Line.ForeColor.RGB = lngColors(lngIndex)
                Select Case objSeries.ChartType
                    Case xlLineMarkers, xlLineMarkersStacked, xlLineMarkersStacked100, _
                            xlRadarMarkers, _
                            xlXYScatter, xlXYScatterLines, xlXYScatterSmooth
                        objSeries.MarkerForegroundColor = lngColors(lngIndex)
                        objSeries.MarkerBackgroundColor = lngColors(lngIndex)
                    Case xlArea, xlAreaStacked, xlAreaStacked100, _
                            xlBarClustered, xlBarStacked, xlBarStacked100, _
                            xlColumnClustered, xlColumnStacked, xlColumnStacked100
                        objSeries.Format.Fill.ForeColor.RGB = lngColors(lngIndex)
                        objSeries.Format.Fill.BackColor.RGB = lngColors(lngIndex)
                End Select
            Next objSeries
        Next objChart
    Next shtTemp

    ' Process chart sheets
    For Each chtTemp In ActiveWorkbook.Charts
        lngIndex = 0
        For Each objSeries In chtTemp.SeriesCollection
            lngIndex = lngIndex Mod 8 + 1
            objSeries.Format.Line.ForeColor.RGB = lngColors(lngIndex)
            Select Case objSeries.ChartType
                Case xlLineMarkers, xlLineMarkersStacked, xlLineMarkersStacked100, _
                        xlRadarMarkers, _
                        xlXYScatter, xlXYScatterLines, xlXYScatterSmooth
                    objSeries.MarkerForegroundColor = lngColors(lngIndex)
                    objSeries.MarkerBackgroundColor = lngColors(lngIndex)
                Case xlArea, xlAreaStacked, xlAreaStacked100, _
                        xlBarClustered, xlBarStacked, xlBarStacked100, _
                        xlColumnClustered, xlColumnStacked, xlColumnStacked100
                    objSeries.Format.Fill.ForeColor.RGB = lngColors(lngIndex)
                    objSeries.Format.Fill.BackColor.RGB = lngColors(lngIndex)
            End Select
        Next objSeries
    Next chtTemp

    MsgBox "All charts have been formatted to the updated colour template.", _
        vbInformation
End Sub

[Rudi]

TX.
Will run and test...

Works great! Cheers! :)