excelloopsif-statementlinechartworksheet-function

Simplifying If Statements Into Loop


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


Solution

  • 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