Change color of axis bars in an Excel pivotchart

Question

I have this PivotChart in Excel 2016

As you can see there are two properties in the axis field: "Date" and "Category".

There are two possible values for "Category": ASC and SBT.

Right now the bars related to either values are of the same colors (Red and Blue).

I want that if the "Category" is SBT, the colors of the bars must be different (for example, yellow and green). How can I achieve that?

Thanks

Answer 1

Try this.

Sub test()
    Dim obj As ChartObject
    Dim cht As Chart
    Dim pnt As Point
    Dim Ws As Worksheet
    Dim s As String

    Set Ws = ActiveSheet
    Set obj = Ws.ChartObjects(1)
    Set cht = obj.Chart

    With cht
        .ApplyDataLabels
        For Each pnt In .SeriesCollection(1).Points
            With pnt.DataLabel
                .ShowCategoryName = True
                .ShowValue = False
            End With
            s = pnt.DataLabel.Text
            If InStr(s, "SBT") Then
               pnt.Format.Fill.ForeColor.RGB = RGB(255, 2255, 0)
            End If
             With pnt.DataLabel
                .ShowCategoryName = False
            End With
        Next pnt
        For Each pnt In .SeriesCollection(2).Points
            With pnt.DataLabel
                .ShowCategoryName = True
                .ShowValue = False
            End With
            s = pnt.DataLabel.Text
            If InStr(s, "SBT") Then
               pnt.Format.Fill.ForeColor.RGB = RGB(29, 219, 22)
            End If
             With pnt.DataLabel
                .ShowCategoryName = False
            End With
        Next pnt
    End With
End Sub