Hi, i've got code that reformats my graphs after a pivot table update and cycles through all the options in the filter (i.e. people) and saves a .pdf - I'm using a stacked bar graph to to show two data points - Renewals Vs Expiries.

When there is data for both, my code formats the two data points seperately and it works (the first time)

If Sheets("Pivots").Range("renewaltest").Value = "OK" Then
' This part of the code formats the renewals data point a bold yellow.

ActiveSheet.ChartObjects("Chart 23").Activate
ActiveChart.SeriesCollection(1).Points(1).Select
    With Selection.format.Fill
        .ForeColor.RGB = RGB(255, 192, 0)
        .Solid
        .Transparency = 0
    End With
    Selection.format.ThreeD.BevelTopInset = 6
    Selection.format.ThreeD.BevelBottomInset = 6
    ActiveChart.SeriesCollection(1).Points(1).ApplyDataLabels
    ActiveChart.SeriesCollection(1).DataLabels.Select
    Selection.ShowSeriesName = True
    With Selection.format.Fill
    .Visible = msoFalse
    End With
    ActiveChart.SeriesCollection(1).Points(1).DataLabel.Select
    Selection.format.TextFrame2.TextRange.Font.Size = 8

' This part of the code formats the expiries data point a washed out grey
    
ActiveChart.SeriesCollection(2).Points(1).Select
    With Selection.format.Fill
        .ForeColor.RGB = RGB(102, 100, 100)
        .Solid
        .Transparency = 0.4
    End With
    Selection.format.ThreeD.BevelTopInset = 6
    Selection.format.ThreeD.BevelBottomInset = 6
    ActiveChart.SeriesCollection(2).Points(1).ApplyDataLabels
    ActiveChart.SeriesCollection(1).DataLabels.Select
    Selection.ShowSeriesName = True
    ActiveChart.SeriesCollection(2).Points(1).DataLabel.Select
    Selection.format.TextFrame2.TextRange.Font.Size = 8
    Selection.ShowSeriesName = True
End If
However, sometimes there will only be data for one or the other. So, there might be a 100% renewal rate or a 100% expiry rate. In which case i wanted the code to format the the chart appropriately (yellow for renewals, a washed out grey for expiries)

When this happens i get my test to only execute the appropriate part of the above code. HOWEVER, if the next person on the filter then has two sets of data again (i.e. data for both Expiries & renewals) Excel no longer seems to recognise the two seperate data points.

Applying formatting to one, applies it to the other. Even when you have only a single data point selected. This happens in the code above AND if you manually go to the chart and try to select the single data point and format it manually. It's like Excel just will not separate the two points.

Has anyone come across this before, and does anyone know of a fix? Have googled but not got anywhere yet.

This is driving me crazy!