Error 91 with Macro that Sets Fill and Line Color in Pivot Charts

[DavidFC]

Greetings

I have a macro that sets the fill and/or line color of 4 different charts to either red, blue, or green.

I recorded the macro as I performed the steps that I would take and this is the code that resulted.

Private Sub Worksheet_Activate()

    ActiveChart.FullSeriesCollection(1).DataLabels.Select
    ActiveChart.FullSeriesCollection(3).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent3
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
        .Solid
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent3
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
    End With
    ActiveChart.FullSeriesCollection(2).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
        .Solid
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
    End With
    ActiveChart.FullSeriesCollection(1).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent2
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
        .Solid
    End With
    ActiveWindow.SmallScroll Down:=12
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent2
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
    End With
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.FullSeriesCollection(3).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent3
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0.1499999762
        .Solid
    End With
    ActiveChart.FullSeriesCollection(2).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0.1499999762
        .Solid
    End With
    ActiveChart.FullSeriesCollection(1).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent2
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0.1499999762
        .Solid
    End With
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.FullSeriesCollection(3).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent3
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0.1499999762
        .Solid
    End With
    ActiveChart.FullSeriesCollection(2).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0.1499999762
        .Solid
    End With
    ActiveChart.FullSeriesCollection(1).Select
    ActiveWindow.SmallScroll Down:=2
    ActiveWindow.LargeScroll Down:=1
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent2
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0.1499999762
        .Solid
    End With
    ActiveSheet.ChartObjects("Chart 3").Activate
    ActiveChart.FullSeriesCollection(3).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent3
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0.1499999762
        .Solid
    End With
    ActiveChart.FullSeriesCollection(2).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0.1499999762
        .Solid
    End With
    ActiveChart.FullSeriesCollection(1).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent2
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0.1499999762
        .Solid
    End With

End Sub

But now I'm getting a Run-time error '91': Object variable or With Block variable not set.

It highlights the first line of code "ActiveChart.FullSeriesCollection(1).DataLabels.Select" when I attempt to debug.

I would really appreciate finding out what I'm missing here.

Thanks a bunch in advance.

[humdingaling]

whole premise of your code is ActiveChart

is the chart actually active when you activate the worksheet?

[DavidFC]

aww fudge...I didn't notice that, and also, it is not....how do I write the code if I simply want the fill and line color of the charts on a specific sheet to be the way they're coded above?

this would have to work if someone is opening the workbook for the first time and or tabbing between sheets

the whole purpose of this code is because excel insists on reformatting my charts with different colors

[humdingaling]

hmmm to be honest you have
since you just recorded your code there is a lot in there you probably don't require

all i actually see you want is
format fill Color
formal fill transparency
Format LINE Color

below is shortened code

Private Sub Worksheet_Activate()
    
    Set cht = ActiveSheet.ChartObjects("Chart 1")
    
    With cht.Chart
        With .FullSeriesCollection(3)
            .Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent3
            .Format.Fill.Transparency = 0.1499999762
            .Format.Line.ForeColor.ObjectThemeColor = msoThemeColorAccent3
        End With
    
        With .FullSeriesCollection(2)
            .Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent1
            .Format.Fill.Transparency = 0.1499999762
            .Format.Line.ForeColor.ObjectThemeColor = msoThemeColorAccent1
        End With
        With .FullSeriesCollection(1)
            .Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent2
            .Format.Fill.Transparency = 0.1499999762
            .Format.Line.ForeColor.ObjectThemeColor = msoThemeColorAccent2
        End With
    End With

End Sub

Replace Chart 1 with whatever your chart is called

[DavidFC]

Quick update:

That code, humdingaling, worked somewhat well but Excel keeps fighting me on the fill and line colors.

I'll keep at it and post the solution should I come upon it.

Thanks for the help.

[humdingaling]

hmmm does not sound "normal"

sounds like some other force at play to be forcing the change in charts on you
cannot really tell just based on the code you have pasted