Autofill scatter chart labels

[G Angie]

Good morning,

I have a big problem. I have a scatter plot and I would like to put label colors based on a column where I have 4 criteria "Act", "Maintain", "Monitor" and "Asset. Labels should be green if it's "Trump", red if it's "Act" etc. I tried a first code but since I have 200 criteria, I have to repeat it 4 times, which is long.
I also tried the code below, but so far I don't have the right format.

Sub Etiquette_13()
    Dim i As Integer
    Dim j As Integer
    Dim Chart As ChartObject
    Set Chart = ActiveSheet.ChartObjects("TRO")
    For i = 1 To Chart.Chart.SeriesCollection.Count
        For j = 1 To Chart.Chart.SeriesCollection(i).Points.Count
            Dim pointValue As String
            pointValue = Range("F" & j).Value
            If pointValue = "Atout" Then
                Chart.Chart.SeriesCollection(i).Points(j).DataLabel.Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
            ElseIf pointValue = "Maintenir" Then
                Chart.Chart.SeriesCollection(i).Points(j).DataLabel.Format.Fill.ForeColor.RGB = RGB(146, 208, 80)
            ElseIf pointValue = "Surveiller" Then
                Chart.Chart.SeriesCollection(i).Points(j).DataLabel.Format.Fill.ForeColor.RGB = msoThemeColorAccent2
            ElseIf pointValue = "Agir" Then
                Chart.Chart.SeriesCollection(i).Points(j).DataLabel.Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
            End If
        Next j
    Next i
End Sub

Can you help me please.

 

[NdNoviceHlp]

Hi G Angie. A couple of things. Using "Chart" as a variable is not good as XL has a specific meaning for "Chart". Not sure how many data labels you need to change on each series or if there is a chance for each series to have more than 1 of your criteria words but you could just "Exit For" to leave the loop (either within the series or within each series or both) to speed things up. To change the data label colour the syntax is like this...

ActiveSheet.ChartObjects("TRO").Chart.SeriesCollection(i).Points(j).DataLabel.Font.ColorIndex = 3

You can adjust the color index to suit or the RGB colour constant may also work. HTH. Dave

 

[G Angie]

Hello, thank you for your answer. But it doesn't work or at least no labels are colored. I have this code below that I reviewed. But for this one, all the labels are filled with the color indicated in the first cell.

That is to say, if my first series label is "Trump" (in column F), the rest of the labels are fixed on "Trump" and are colored in green while their cell contains the other types

Sub FormatConditionnelGraphique_etiquete883()
    Dim c As Integer
    Dim d As Integer

    Dim Chart As ChartObject
    Dim TypeTable As ListObject
    Dim TypeColumn As Range
    
    Set Chart = ActiveSheet.ChartObjects("TRO")
    Set TypeTable = ThisWorkbook.Worksheets("Feuil3").ListObjects("Type")
    Set TypeColumn = TypeTable.ListColumns("Type").DataBodyRange

    For c = 1 To Chart.Chart.SeriesCollection.Count
        For d = 1 To Chart.Chart.SeriesCollection(c).Points.Count
            If TypeColumn(i) = "Atout" Then
                Chart.Chart.SeriesCollection(c).Points(d).DataLabel.Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
            ElseIf TypeColumn(i) = "Surveiller" Then
                Chart.Chart.SeriesCollection(c).Points(d).DataLabel.Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent2
                Chart.Chart.SeriesCollection(c).Points(d).DataLabel.Format.Fill.ForeColor.TintAndShade = 0
                Chart.Chart.SeriesCollection(c).Points(d).DataLabel.Format.Fill.ForeColor.Brightness = 0
            ElseIf TypeColumn(i) = "Agir" Then
                Chart.Chart.SeriesCollection(c).Points(d).DataLabel.Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
            ElseIf TypeColumn(i) = "Maintenir" Then
                Chart.Chart.SeriesCollection(c).Points(d).DataLabel.Format.Fill.ForeColor.RGB = RGB(146, 208, 80)
            End If
        Next d
    Next c
End Sub

 

[NdNoviceHlp]

I guess you can't just explicitly state the colour for a data label. Seems like U should be able to. This works...

With ActiveSheet.ChartObjects("TRO").Chart.SeriesCollection(i).Points(j).DataLabel
.Font.ColorIndex = 3
End With

It would be better to replace Active sheet with the sheet name or CHANGE your "Chart" variable name to something that is not an XL term. Dave