I have code that changes the line color in an Excel line chart based on a cell value but would like to simplify it if possible.
This is my code:
Sub colorSegment()
Dim ws As Worksheet
Dim cht As Chart
Set ws = ActiveSheet
Set cht = ws.ChartObjects("Chart 1").Chart
If Cells(3, 2) >= Cells(2, 2) Then
With cht.SeriesCollection(1)
.Points(2).Format.Line.ForeColor.RGB = RGB(0, 255, 0)
End With
Else
With cht.SeriesCollection(1)
.Points(2).Format.Line.ForeColor.RGB = RGB(255, 0, 0)
End With
End If
If Cells(4, 2) >= Cells(3, 2) Then
With cht.SeriesCollection(1)
.Points(3).Format.Line.ForeColor.RGB = RGB(0, 255, 0)
End With
Else
With cht.SeriesCollection(1)
.Points(3).Format.Line.ForeColor.RGB = RGB(255, 0, 0)
End With
End If
If Cells(5, 2) >= Cells(4, 2) Then
With cht.SeriesCollection(1)
.Points(4).Format.Line.ForeColor.RGB = RGB(0, 255, 0)
End With
Else
With cht.SeriesCollection(1)
.Points(4).Format.Line.ForeColor.RGB = RGB(255, 0, 0)
End With
End If
If Cells(6, 2) >= Cells(5, 2) Then
With cht.SeriesCollection(1)
.Points(5).Format.Line.ForeColor.RGB = RGB(0, 255, 0)
End With
Else
With cht.SeriesCollection(1)
.Points(5).Format.Line.ForeColor.RGB = RGB(255, 0, 0)
End With
End If
If Cells(7, 2) >= Cells(6, 2) Then
With cht.SeriesCollection(1)
.Points(6).Format.Line.ForeColor.RGB = RGB(0, 255, 0)
End With
Else
With cht.SeriesCollection(1)
.Points(6).Format.Line.ForeColor.RGB = RGB(255, 0, 0)
End With
End If
I would like to use a Loop but I can't figure out how to do this with changing values.
Here is a snapshot of what I'm working with.
Assuming I got the logic of your IF
statements correctly, I think it could be simplified to:
Sub colorSegment()
Dim ws As Worksheet
Dim cht As Chart
Dim i As Integer
Set ws = ActiveSheet
Set cht = ws.ChartObjects("Chart 1").Chart
' Loop through the cells and color the corresponding points
For i = 3 To 7
If ws.Cells(i, 2).Value >= ws.Cells(i - 1, 2).Value Then
cht.SeriesCollection(1).Points(i - 1).Format.Line.ForeColor.RGB = RGB(0, 255, 0) ' Green
Else
cht.SeriesCollection(1).Points(i - 1).Format.Line.ForeColor.RGB = RGB(255, 0, 0) ' Red
End If
Next i
End Sub