0

I have this PivotChart in Excel 2016

enter image description here

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

Community
  • 1
  • 1
Ponzaro
  • 139
  • 2
  • 5
  • 16

1 Answers1

0

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
Dy.Lee
  • 7,527
  • 1
  • 12
  • 14
  • It could be good! Do you know if there is a way to change the legend to add the other color to the category? – Ponzaro Oct 09 '17 at 19:46
  • @Ponzaro, The legnend color is setted according to seriescollection's color. – Dy.Lee Oct 09 '17 at 23:49
  • so, if you want to change legend color, you change collection 's color. .SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(255, 0, 0) – Dy.Lee Oct 09 '17 at 23:50