VBA - Change chart line colour

Question

I've been attempting to get a VBA script that will update chart line colours based on an RGB reference that will change based on a score.

I would also like to understand:

1. How to exclude some sheets from the update
2. How to exclude some series from the update (they always remain grey)

My current script is:

Sub UpdateAllChartLines

Dim sht As Worksheet
Dim CurrentSheet As Worksheet
Dim cht As ChartObject
Dim ser As Series

Application.ScreenUpdating = False
Application.EnableEvents = False

Set CurrentSheet = ActiveSheet
Set myRed = Worksheets("TextElements").Range("K6")
Set myGreen = Worksheets("TextElements").Range("K7")
Set myBlue = Worksheets("TextElements").Range("K8")
Set myThickness = Worksheets("TextElements").Range("E37")

For Each sht In ActiveWorkbook.Worksheets
  For Each cht In sht.ChartObjects
    For Each ser In cht.SeriesCollection
        cht.Activate
                With ActiveChart.SeriesCollection
                .Format.Line.Visible = msoFalse
                .Format.Line.Visible = msoTrue
                .Format.Line.ForeColor.RGB = RGB(myRed, myGreen, myBlue)
                .Format.Line.Weight = myThickness
                 End With
    Next ser
   Next cht
Next sht

CurrentSheet.Activate
Application.EnableEvents = True

End Sub

My issue recently has been that I get a debug error at

For Each ser In cht.SeriesCollection

As ever any help greatly appreciated.

Answer 1

I've marked Tim's response up as the answer, but thought I'd add in my final working script to help anyone in future. I suspect that this could be simplified, but I couldn't get it to work with ser.name = x OR y OR z Then Tim's answer also for unknown reasons added markers to the first series on each graph, but this was easily fixed by adding the .MarkerStyle = xlMarkerStyleNone command.

Thanks again to Tim and to everyone else who commented.

J

Sub UpdateAllChartLines()

    Dim sht As Worksheet
    Dim chtObj As ChartObject
    Dim ser As Series
    Dim clr As Long

    Application.ScreenUpdating = False

    With Worksheets("TextElements")
        clr = RGB(.Range("K6").Value, .Range("K7").Value, .Range("K8").Value)
        myThickness = .Range("K9").Value
    End With

    For Each sht In ActiveWorkbook.Worksheets
        If sht.Name <> "Sheet1" Then '<<< can exclude sheets here
        If sht.Name <> "Sheet2" Then
            For Each chtObj In sht.ChartObjects
                For Each ser In chtObj.Chart.SeriesCollection
                        If ser.Name <> "Series1" Then
                        If ser.Name <> "Series2" Then
                        If ser.Name <> "Series3" Then
                        If ser.Name <> "Series4" Then
                            With ser
                                .MarkerStyle = xlMarkerStyleNone
                                .Format.Line.Visible = msoTrue
                                .Format.Line.ForeColor.RGB = clr
                                .Format.Line.Weight = myThickness
                                .Format.Glow.Radius = 0
                            End With
                        End If
                        End If
                        End If
                        End If
                Next ser
            Next chtObj
        End If
        End If
    Next sht

    Application.ScreenUpdating = True

End Sub

Answer 2

You'd need to clarify on what basis you want to exclude things, but something like this should work.

Note there's no need to activate/select anything in order to change it.

Sub UpdateAllChartLines()

    Dim sht As Worksheet
    Dim chtObj As ChartObject
    Dim ser As Series
    Dim clr As Long, sNum

    Application.ScreenUpdating = False

    With Worksheets("TextElements")
        clr = RGB(.Range("K6").Value, .Range("K8").Value, .Range("K8").Value)
        myThickness = .Range("E37").Value
    End With

    For Each sht In ActiveWorkbook.Worksheets
        If sht.Name <> "IgnoreThis" Then '<<< can exclude sheets here
            For Each chtObj In sht.ChartObjects
                For Each sNum in Array(5,6,7,8) '<<<edit
                'For Each ser In chtObj.Chart.SeriesCollection

                        With chtObj.Chart.SeriesCollection(sNum)
                            .Format.Line.Visible = msoTrue
                            .Format.Line.ForeColor.RGB = clr
                            .Format.Line.Weight = myThickness
                        End With

                'Next ser
                Next sNum
            Next chtObj
        End If
    Next sht

End Sub