Checking a Range for Cell Colors Based On A Certain Row-CodePudding

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

CodePudding user response：

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

CodePudding user response：

Removing the repetition:

For Each cell In Range("F3:F66").Cells
    If cell.Value > 7 Then
        With cell.Offset(0, -2)
            Select Case .Value
                Case "Running": .Value = "DEAD"
                Case "Ended/Running": .Value = "ENDED/DEAD"
            End Select
        End With

        'Here `Range` is *relative* to `EntireRow`
        For Each DailyEarningsCell In cell.EntireRow.Range("H1:AL1").Cells 
            If DailyEarningsCell.Interior.ColorIndex = 2 Then
                DailyEarningsCell.Interior.ColorIndex = 1
            End If
        Next DailyEarningsCell
    End If '>7
Next cell