Jeff1959
04-11-2009, 01:47 PM
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:banghead:
My code so far
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
Thanks
Still a newbie:banghead:
My code so far
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