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.

Line Chart

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.

Line Chart

• excel
• loops
• if-statement
• linechart
• worksheet-function

1 Answer 1

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