Need to change colors on the Pivot Chart using VBA

Question

Problem:

I am generating reports using Excel 2010, and multiple pivot charts. When I generate reports I can not set the colors of the pivot chart series to a static value. Some times "Pass" series displayed as "RED" and this creates confusion.

I try to use the code below to force to change the colors on the series:

Sheets("PSD").Select
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.SeriesCollection(1).Select
With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(0, 176, 80)
    .Transparency = 0
    .Solid
End With

The problem with the code is that SeriesCollection(1) is not always the same series I want and when I update the code as SeriesCollection("Pass"), it does NOT work.

I need to find a way to refer the SeriesCollection by name, and if it does NOT there I can continue using On Error Resume Next no need to check it.

Answer 1

To get a handle on a series by it's name you can do this:

Sub cht()
    Dim cht As Chart
    Set cht = Sheets("PSD").ChartObjects("Chart 5").Chart

    Dim ss As Series
    Set ss = cht.SeriesCollection("Pass")

    With ss.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 176, 80)
    End With
End Sub

---

Before:

After: