Excel VBA: Color range based on cell value

Question

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!

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

Conditional Formatting will do this without the need of VBA. Is there a reason you want to do this in VBA? — Scott Craner
– Scott Craner, Commented Jul 1, 2016 at 17:17

Kyle · Accepted Answer · 2016-07-01 17:17:24Z

3

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.

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

answered Jul 1, 2016 at 17:17

Kyle

2,5452 gold badges19 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA: Color range based on cell value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related