CODEWITHSUNDEEP
excelvbacharts
cryocaustik
cryocaustik

Reputation: 479

Format a PivotChart data label based on the x axis

I am trying to find a way (if possible) to change the text color of a data label to red, if the X axis happens to be a certain label.

The issue I am having is that my charts are dynamic (are refreshed daily via PowerPivot and have date and other slicers that change what data is displayed in the charts), so even if I color the data label red, when the charts are refreshed, it reverts back to the original black font.

For example, in the image below, I have a chart that is tracking tickets by the amount of days that they have been open; the goal is to have all tickets resolved within 45 days; so, when the charts shows any data in the "45+" section, I would like the font of that data label to be red.

Sample of my chart:

Sample of my chart:

In my research I was able to find and modify some code that make all the data labels red, but I am very new to using VBA to manipulate charts and would appreciate some help. The code I have so far:

Sub ChrtTest()
Dim i As Long

Sheets("Dashboard").Select
ActiveSheet.ChartObjects("DB_Chrt_1").Activate
ActiveChart.PlotArea.Select

With ActiveChart
    For i = 1 To .SeriesCollection.Count
        With .SeriesCollection(i).Format.Fill
            Select Case .Parent.Parent.Name
                Case "45+"
                    .Visible = msoTrue
                    With .Parent.Parent
                        .ApplyDataLabels
                        With .DataLabels
                            .Position = xlLabelPositionOutsideEnd
                            .Font.Color = vbYellow
                        End With
                    End With
                Case Else
                    .Visible = msoTrue
                    With .Parent.Parent
                        .ApplyDataLabels
                        With .DataLabels
                            .Position = xlLabelPositionOutsideEnd
                            .Font.Color = vbRed
                        End With
                    End With
             End Select
        End With
        Next i
      End With

End Sub

Upvotes: 0

Views: 830

Answers (1)

MacroMarc
MacroMarc

Reputation: 3324

Some code to get you started:

Sub template()

    Dim pc As Chart
    Set pc = Sheets("Dashboard").ChartObjects("DB_Chrt_1").Chart

    Dim ax As Axis
    Set ax = pc.Axes(xlCategory)

    Dim dSet As Series
    Set dSet = pc.SeriesCollection(1)

    Dim dPoints As Points
    Set dPoints = dSet.Points

    pc.ApplyDataLabels

    For i = 1 To dPoints.Count
        If ax.CategoryNames(i) = "45+" Then
            With dPoints(i).DataLabel.Format.TextFrame2.TextRange.Characters
               .Font.Fill.ForeColor.RGB = RGB(255, 0, 0)
            End With
        End If
   Next i

End Sub

This should set the dataLabel text to red. You can fool around with other properties as well

Upvotes: 1

Related Questions