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:

Answer 2

I was just working on this.

you can evaluate the name of the indexed seriescollection and then act according to it. here is my example for 8 (hard coded) situations that I have on my possibilities of series, but i guess it can be dynamic somehow.

 Sub Format_GraphPertes_TRG(PV_Chart_Name)
    On Error Resume Next
    Dim i
    
    
    ActiveSheet.ChartObjects(PV_Chart_Name).Activate

    
'== Balayage des legendes dans le chart
For i = 1 To 8
    
    ActiveChart.FullSeriesCollection(i).Select
    'MsgBox ActiveChart.FullSeriesCollection(i).Name
    
    Select Case Left(ActiveChart.FullSeriesCollection(i).Name, 2)
    
    Case "01"        
        With Selection.Format.Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(0, 176, 80)
            .Transparency = 0
            .Solid
        End With
     
    Case "02"
        With Selection.Format.Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(255, 0, 0)
            .Transparency = 0
            .Solid
        End With
        
    Case "03"
        With Selection.Format.Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(255, 255, 0)
            .Transparency = 0
            .Solid
        End With
        
    Case "04"
        With Selection.Format.Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(174, 171, 171)
            .Transparency = 0
            .Solid
        End With
   
        
    Case "05"
        With Selection.Format.Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(72, 161, 250)
            .Transparency = 0
            .Solid
        End With
    
         
    Case "06"
        With Selection.Format.Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(201, 43, 152)
            .Transparency = 0
            .Solid
        End With
    
        
    Case "07"
        With Selection.Format.Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(47, 103, 153)
            .Transparency = 0
            .Solid
        End With
        
    End Select
    
    
Next

    
    
End Sub