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	Criteria1	Criteria 2	Status
Customer1	5	$3	Won
Customer2	2	$9	Won
Customer3	12	$25	Active
Customer4	7	$11	Lost
Customer5	9	$14	Won

<tbody>
</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