Adding Data Label To Chart Based On X Values

Question

I have a chart with xvalues Green, Blue, and Red and I want to only add Data Labels to the Red Values. But the colors aren't always in the same order, so I cant use some thing like: Chart.SeriesCollection(n).Points(j).DataLabel.Delete

I want to be able to do something like this: If Chart.SeriesCollection(n).Points(j).DataLabel.XValues = "Red" Then Chart.SeriesCollection(n).Points(j).DataLabel.Delete

But the .XValues command in vba doesnt work like that. Any suggestions? Thanks in advance.

Answer 1

In your description, you say that you want to add data labels to the Red XValues, yet your code deletes them. So I am going to assume that you want to delete the data labels for the Red XValues. Change the chart reference accordingly.

Dim sr As Series
Dim n As Long
Dim j As Long

With Worksheets("Sheet1").ChartObjects("Chart 1").Chart
    For n = 1 To .SeriesCollection.Count
        Set sr = .SeriesCollection(n)
        sr.HasDataLabels = True
        For j = 1 To sr.Points.Count
            If sr.XValues()(j) = "Red" Then
                sr.Points(j).DataLabel.Delete
            End If
        Next j
    Next n
End With

Answer 2

No need for VBA.

Here's my setup. Categories, including maybe "Red" in column A, Values in column B, and chart labels in column C. In cell C2 I'm using a formula like this:

=IF(A2="Red","Label Text Here","")

so there is only text in that column if the X value is "Red".

My chart plots columns A and B of the data range. I added data labels, then formatted the data labels to use Values from Cells, and selected C2:C7 for the cells with labels.

The nice thing is it's dynamic. If the data changes, the labels update without having to add the labels back and rerunning any code.