Excel VBA：基于单元格值的颜色范围

I am writing code so that when the value of a cell has a certain value it highlights a range for that row (columns G-O, but not the entire row). The code below is recognizing the values of "c" correctly but coloring random rows. For example, when row 2 (O2) has a value under 40, it colors row 4. Please help!

我正在编写代码,以便当单元格的值具有特定值时,它会突出显示该行的范围(列G-O,但不是整行)。下面的代码正确识别“c”的值,但着色随机行。例如,当第2行(O2)的值小于40时,它会为第4行着色。请帮忙!

Sub color()

    Dim lastrow As Long
    Dim c As Variant

    lastrow = Range("o" & Rows.Count).End(xlUp).Row
    For Each c In Range("O1:O" & lastrow)
        If c.Value < 40 Then
             ' MsgBox (c)
             Range(Cells(c, 7), Cells(c, 15)).Interior.ColorIndex = 7 
        End If
    Next c

End Sub

1 个解决方案

#1

See changes below. It has to do with how you are using Cells(). The way you have it, it will use the value of "c", not the row.

请参阅以下更改。它与你如何使用Cells()有关。你拥有它的方式,它将使用“c”的值,而不是行。

Sub color()

Dim lastrow As Long
Dim c As Variant
lastrow = Range("o" & Rows.Count).End(xlUp).Row
    For Each c In Range("O1:O" & lastrow)
        If c.Value < 40 Then
            ' MsgBox (c)
             Range(Cells(c.Row, 7), Cells(c.Row, 15)).Interior.ColorIndex = 7 
        End If
    Next c

End Sub