基于某一行检查单元格颜色的范围

Question

此代码应该检查一列中是否有大于7的数字，并更改另一个单元格的值。它做得很好。但是，如果这是真的(它发现一个大于7(，我想检查该行中的单元格范围并更改单元格颜色。我不知道如何用H(那一行(:AL(那一排(代替H3:AL3。

我希望我说得有道理。非常感谢您的帮助。谢谢！！：(

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

Answer 1

您引用了用Cell推断的范围对象的.Row属性。

因此：

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

Answer 2

删除重复：

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