VBA conditional formatting on Pivot Charts?

[Pebbaroo]

Hi,

I'm trying to create a dashboard with a number of Pivot Charts. Ideally, I want to include some code in the work sheet that updates the bar colours of the chart depending on the value of the Pivot Chart (red when it's negative, blue when it's positive).

The dashboard is a visual representation of the outcome of a number of experiments we are running on multivariate testing. The experiments are included in the Pivot Charts as filters, which update the charts with the relevant data for that particular experiment. Each experiment has a different number of variants, meaning creating a 'normal' chart from the pivots first will not work because I need it to be able to pick up the varying number of potential rows, and ignore the 'empty' rows if no data is there.

This is the code I was using on the charts before I turned them into pivots (it was working back then). Is there a way of achieving this once they are pivot charts?

Dim s As Series
Dim i As Long


ActiveSheet.ChartObjects("Chart 41").Activate
    For Each s In ChartObjects("Chart 41").Chart.SeriesCollection


            For i = 1 To s.Points.Count
                If s.Values(i) < 0 Then
                s.Points(i).Interior.Color = RGB(192, 80, 77)
                Else
                s.Points(i).Interior.Color = RGB(75, 172, 198)
                End If
            Next i
    Next s