Custom Data Labels for Scatter Plot

[white84]

I have data in a table such as this example, tracking recentcompetitions. Most data is in pivot table, but then cells are linked to astandard table. I have conditional formatting to highlight the status of the competition based on Active/Won/Lost (No color/Green/Red).

This is then linked to an XY Scatter plot based on this criteria, with data labelson the scatter plot only showing the customer name, and a box around the namecolored to correspond to the Green/Red Won/Lost status.

When I update monthly and refresh the pivot table, the conditionalformatting works fine in the table but formatting of the data labels on thechart get jumbled (Some active turn a color, some Won/Lost will switch color).
Is there a way to link or set formatting based on a sortof lookup, so I can refresh the data and don’t have to reset the data labelseach time. (Numerous customers, numerous criteria).

Thanks in advance!

Customer Name

<tbody>
[TD="width: 156, bgcolor: transparent"] Criteria1
[/TD]
[TD="width: 156, bgcolor: transparent"] Criteria 2
[/TD]
[TD="width: 156, bgcolor: transparent"] Status
[/TD]

[TD="width: 156, bgcolor: transparent"]Customer1

[/TD]
[TD="width: 156, bgcolor: transparent"] 5
[/TD]
[TD="width: 156, bgcolor: transparent"] $3
[/TD]
[TD="width: 156, bgcolor: transparent"] Won
[/TD]

[TD="width: 156, bgcolor: transparent"] Customer2
[/TD]
[TD="width: 156, bgcolor: transparent"] 2
[/TD]
[TD="width: 156, bgcolor: transparent"] $9
[/TD]
[TD="width: 156, bgcolor: transparent"] Won
[/TD]

[TD="width: 156, bgcolor: transparent"] Customer3
[/TD]
[TD="width: 156, bgcolor: transparent"] 12
[/TD]
[TD="width: 156, bgcolor: transparent"] $25
[/TD]
[TD="width: 156, bgcolor: transparent"] Active
[/TD]

[TD="width: 156, bgcolor: transparent"] Customer4
[/TD]
[TD="width: 156, bgcolor: transparent"] 7
[/TD]
[TD="width: 156, bgcolor: transparent"] $11
[/TD]
[TD="width: 156, bgcolor: transparent"] Lost
[/TD]

[TD="width: 156, bgcolor: transparent"] Customer5
[/TD]
[TD="width: 156, bgcolor: transparent"] 9
[/TD]
[TD="width: 156, bgcolor: transparent"] $14
[/TD]
[TD="width: 156, bgcolor: transparent"] Won
[/TD]

</tbody>

 

[white84]

IT WORKED!

Thank you VERY much!

For anyone else interested here is my final code. I decided to remove the [White] formatting from "Active" so there was not as much overlap. I also changed the font color to black.

Sub FormatLabels()
Dim s As Series, y, dl As DataLabel, i%, r As Range
Set r = [k5]
Set s = ActiveSheet.ChartObjects("chartA320").Chart.SeriesCollection(1)
y = s.Values
For i = LBound(y) To UBound(y)
    Set dl = s.Points(i).DataLabel
    Select Case r
        Case Is = "Won"
            dl.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)
            dl.Format.Fill.ForeColor.RGB = RGB(5, 250, 5)
        Case Is = "Lost"
            dl.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)
            dl.Format.Fill.ForeColor.RGB = RGB(250, 5, 5)
    End Select
    Set r = r.Offset(1)
Next
End Sub