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>

 

[Worf]

You can use code like the example below, which formats the data labels based on the status column:

[FONT=Courier New][SIZE=3][COLOR=#000000] 
Sub FormatLabels()
Dim s As Series, y, dl As DataLabel, i%, r As Range
Set r = [p141]                  ' where the information starts
Set s = ActiveChart.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(250, 250, 5)
            dl.Format.Fill.ForeColor.RGB = RGB(5, 250, 5)
        Case Is = "lost"
            dl.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(250, 250, 5)
            dl.Format.Fill.ForeColor.RGB = RGB(250, 5, 5)
        Case Is = "active"
            dl.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(5, 5, 5)
            dl.Format.Fill.ForeColor.RGB = RGB(250, 250, 250)
    End Select
    Set r = r.Offset(1)
Next
End Sub<strike></strike>
[/COLOR][/SIZE][/FONT]

 

[white84]

Hi Worf! Thank you for the suggestion! I think this is just what I need, but I am having trouble getting it to work. I the r value to the first cell in my range of "Won/Active/Lost" but something is still amiss. Could you offer one more hint?

Thanks again!

 

[Worf]

My example expects the words in lowercase, can you check that?

 

[white84]

My example expects the words in lowercase, can you check that?

My first attempt at troubleshooting was to adjust the case, but I still receive a Run-time error '91': Object variable or With block variable not set

I have this exact code (my data begins in J5)

Sub FormatLabels()
Dim s As Series, y, dl As DataLabel, i%, r As Range
Set r = [j5]
Set s = ActiveChart.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(250, 250, 5)
            dl.Format.Fill.ForeColor.RGB = RGB(5, 250, 5)
        Case Is = "Lost"
            dl.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(250, 250, 5)
            dl.Format.Fill.ForeColor.RGB = RGB(250, 5, 5)
        Case Is = "Active"
            dl.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(5, 5, 5)
            dl.Format.Fill.ForeColor.RGB = RGB(250, 250, 250)
    End Select
    Set r = r.Offset(1)
Next
End Sub

 

[Worf]

What line throws the error?

 

[white84]

What line throws the error?

Sorry, I am not sure how to determine that. The pop-up occurs after I click Run, but no additional details are provided.

 

[Worf]

The offending code line should be highlighted in yellow when the error occurs.

 

[white84]

The offending code line should be highlighted in yellow when the error occurs.

No yellow highlighting. I tried saving the code, then assigning the macro to the chart. When I click on the chart it gives me an error "Object variable or With block variable not set"

My Won/Lost/Active data is in cells J5:J41. I have tried [j5] and [j5:j41] but I receive the same error.

 

[Worf]

I was not assigning the code to the chart, try version below:

Sub FormatLabels()
Dim s As Series, y, dl As DataLabel, i%, r As Range
Set r = [f110]                                                        ' starting cell
Set s = ActiveSheet.ChartObjects("chart14").Chart.SeriesCollection(1) ' your chart name here
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(250, 250, 5)
            dl.Format.Fill.ForeColor.RGB = RGB(5, 250, 5)
        Case Is = "Lost"
            dl.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(250, 250, 5)
            dl.Format.Fill.ForeColor.RGB = RGB(250, 5, 5)
        Case Is = "Active"
            dl.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(5, 5, 5)
            dl.Format.Fill.ForeColor.RGB = RGB(250, 250, 250)
    End Select
    Set r = r.Offset(1)
Next
End Sub