Checking a Range for Cell Colors Based On A Certain Row

January 28, 2022

This code should check a column for numbers greater than 7, and change the value of another cell. It does this fine. But then, if that is true (it finds one greater than 7), I want to check a range of cells in THAT row and change cell colors. I’m not sure how to replace H3:AL3 with H(that row):AL(that row).

I hope I am making sense. Any help is greatly appreciated. Thanks!! 🙂

For Each Cell In Range("F3:F66")
If Cell.Value > 7 And Cell.Offset(0, -2).Value = "Running" Then
    Cell.Offset(0, -2).Value = "DEAD"
    For Each DailyEarningsCell In Range("H3:AL3")
        If DailyEarningsCell.Interior.ColorIndex = 2 Then
            DailyEarningsCell.Interior.ColorIndex = 1
        End If
    Next DailyEarningsCell
ElseIf Cell.Value > 7 And Cell.Offset(0, -2).Value = "Ended/Running" Then
    Cell.Offset(0, -2).Value = "ENDED/DEAD"
    For Each DailyEarningsCell In Range("H3:AL3")
        If DailyEarningsCell.Interior.ColorIndex = 2 Then
            DailyEarningsCell.Interior.ColorIndex = 1
        End If
    Next DailyEarningsCell
End If
Next Cell

>Solution :

You refer to the .Row property of the range object which you are inferring with Cell.

So:

For Each Cell In Range("F3:F66")
If Cell.Value > 7 And Cell.Offset(0, -2).Value = "Running" Then
    Cell.Offset(0, -2).Value = "DEAD"

    For Each DailyEarningsCell In Range("H" & Cell.Row & ":AL" & Cell.Row)
        If DailyEarningsCell.Interior.ColorIndex = 2 Then
            DailyEarningsCell.Interior.ColorIndex = 1
        End If
    Next DailyEarningsCell
ElseIf Cell.Value > 7 And Cell.Offset(0, -2).Value = "Ended/Running" Then
    Cell.Offset(0, -2).Value = "ENDED/DEAD"
    For Each DailyEarningsCell In Range("H" & Cell.Row & ":AL" & Cell.Row)
        If DailyEarningsCell.Interior.ColorIndex = 2 Then
            DailyEarningsCell.Interior.ColorIndex = 1
        End If
    Next DailyEarningsCell
End If
Next Cell