Color Points (Bars) in Pivot Chart based on Row Labels (Axis Fields)

Question

I'm trying to automate a process that so far I have been doing manually in Excel 2010. I create Pivot Charts often. One of the series on these charts is displayed as bars. I change the fill color of each bar based on one of the row labels of the pivot chart. For instance, if the row label = "GEO", I change the fill color of the bar to green.

I'm sure that it's possible to automate this process through VBA. Here's my code so far. When I run this macro, it stops at the first line of the If statement and gives this error. Compile error: Expected array. Can anyone give me some advice as to how to make this code work?

Sub By_Rig_PC_Coloring()

For i = 1 To ActiveChart.SeriesCollection(2).Points.Count
    ActiveChart.SeriesCollection(2).Points(i).Select
    If xlRowField("MFR") = "GEO" Then
        Selection.Format.Fill.Forcolor.RGB = RGB(0, 176, 80)
    End If
Next i

End Sub

Try to record your manual actions using macro recorder - this will definitely give you the right way to address objects. — Maks Gordeev
I tried this method initially, but, I simply select the ActiveChart.SeriesCollection (2).Point(x), where "x" is whatever bar I select. — user2096344

AdamL AdamL · Accepted Answer · 2015-08-06T13:27:30

As far as I know, you cannot access X axis labels associated with current Point. But this is a PIVOT chart, so you can use your pivot table to get the info you need.

Points are DataField's PivotItems, Series are ColumnFields and X labels are RowFields. So SeriesCollection(2).Points(10) will be pvtYourPivotTable.ColumnFields(2).DataRange.Cells(10) (assuming that you have only one DataField, otherwise DataRange will be multi-column, and you'll have to adjust for that). So once you have a cell in pivot table assosacietd with Point, the label will be located at Intersect(pvtYourPivotTable.RowFields("MFR").DataRange, pvtYourPivotTable.ColumnFields(2).DataRange.Cells(10).EntireRow. You can also use Offset or other method.

Here is another method of colouring chart bars based on X labels: Peltier Tech Blog without VBA.

Hope this helps.

Color Points (Bars) in Pivot Chart based on Row Labels (Axis Fields)

1 Answers