Change series colour for all charts on a worksheet.

Hello. This is my first post on this forum. ...I'll be much obliged by any help offered to this problem! I have a worksheet where I need to set all of the colours of my charts back to a default palette. I was exploring two options:

Sub ColourChart()
Dim oChart As ChartObject
For Each oChart In ActiveSheet.ChartObjects
With oChart
.SeriesCollection(1).Interior.Color = RGB(0, 77, 154)
.SeriesCollection(2).Interior.Color = RGB(191, 191, 191)
.SeriesCollection(3).Interior.Color = RGB(127, 127, 127)
.SeriesCollection(4).Interior.Color = RGB(74, 140, 213)
.SeriesCollection(5).Interior.Color = RGB(100, 100, 100)
End With
Next oChart

As some charts have fewer than 5 series, I thought it best to use something like the following:

Sub ColourChart()
Dim i As Integer
Dim oChart As ChartObject

For Each oChart In ActiveSheet.ChartObjects
For i = 1 To oChart.SeriesCollection.Count(i)
With oChart.SeriesCollection(i)
   Select Case i
      Case "1": .Interior.Color = RGB(0, 77, 154)
      Case "2": .Interior.Color = RGB(191, 191, 191)
      Case "3": .Interior.Color = RGB(127, 127, 127)
      Case "4": .Interior.Color = RGB(74, 140, 213)
      Case "5": .Interior.Color = RGB(100, 100, 100)
    End Select
End With
Next i
Next oChart
End Sub

However, both return errors of the type: "Object doesn't support this property or method", highlighting the first line after the "With" statement... though I suspect there may be errors beyond just that...

I'm using Excel 2013. The series on the different charts are not named 1-5, but there are usually 5 of them.

Again - many thanks.

Hi,

Try changing

For i = 1 To oChart.SeriesCollection.Count(i)

to

For i = 1 To oChart.SeriesCollection.Count

You shouldn't need to reference "i" to return the count of SeriesCollection.

If this doesn't help, can you please upload a workbook so that I may debug your code in context?

Hope this helps

Hi,

Try this code:

Sub ColourCharts()
    Dim i As Integer
    Dim oChart As ChartObject
    
    For Each oChart In ActiveSheet.ChartObjects
        For i = 1 To oChart.Chart.SeriesCollection.Count
            With oChart.Chart.SeriesCollection(i)
                Select Case i
                    Case "1": .Interior.Color = RGB(0, 77, 154)
                    Case "2": .Interior.Color = RGB(191, 191, 191)
                    Case "3": .Interior.Color = RGB(127, 127, 127)
                    Case "4": .Interior.Color = RGB(74, 140, 213)
                    Case "5": .Interior.Color = RGB(100, 100, 100)
                End Select
            End With
        Next i
    Next oChart
End Sub

The problem was that a "ChartObject" (oChart) doesn't have a "SeriesCollection" attached to it, but it does have a "Chart" element which does have a series collection. So as you can see, the adjustments are very minor, and you were very close.

Hope this helps

Excellent. This works perfectly - you guys do a great service on forums like these, thank you.

You're welcome!

Please don't forget to mark this thread as solved