Loop through chart and Highlight chart labels based on value

[Jeff1959]

Hi, I have been struggling with this for a week and still have not figured out the logic. What I want to do is loop through the chart series collections and based on a value from an excel range i.e.(“B18”) change the font color to red or black. Please see file that I have included.

Thanks
Still a newbie




My code so far
[vba]Sub CrtGoal()
Dim i As Integer
Dim c As Integer
Dim x() As Variant
Dim G() As Variant

c = 2

ActiveSheet.ChartObjects("Chart 2").Activate

G = ActiveChart.SeriesCollection(1).Values

x = ActiveChart.SeriesCollection(c).Values
For i = LBound(x) To UBound(x)
x = ActiveChart.SeriesCollection(c).Values
For c = 2 To 5

If (i = 1) Then
If (x(i)) >= (G(i) * (ActiveWorkbook.Worksheets("SumByDay").Cells(18, 2))) Then
With ActiveChart.SeriesCollection(3).Points(1)
.DataLabel.Font.ColorIndex = 3
Debug.Print "Point "; (G(i) * (ActiveWorkbook.Worksheets("SumByDay").Cells(18, 2)))
End With
Else
With ActiveChart.SeriesCollection(3).Points(1)
.DataLabel.Font.ColorIndex = 3
Debug.Print "Point "; (G(i) * (ActiveWorkbook.Worksheets("SumByDay").Cells(18, 2)))
End With

End If
End If
Next c
Next i


End Sub[/vba]

[Dave]

In the abscence of anything else, here's some code to get you started. Perhaps with some adaptation and help,you'll get this to work. HTH. Dave
[VBA]
Sub ChangeLabel2()
'changes data labels to red if "C" is false
'changes data labels to green if "C" is true
Dim Pt As Point, Cnt As Integer
For Each Chart In ChartObjects
Chart.Activate
For Each Pt In ActiveChart.SeriesCollection(1).Points
Cnt = Cnt + 1
On Error Resume Next
If Sheets("sheet1").Range("C" & Cnt).Value = False Then
With ActiveChart.SeriesCollection(1).Points(Cnt).DataLabel
.Font.ColorIndex = 3
End With
Else
With ActiveChart.SeriesCollection(1).Points(Cnt).DataLabel
.Font.ColorIndex = 4
End With
End If
Next Pt
Next Chart
End Sub
[/VBA]