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

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

Collectives™ on Stack Overflow

Excel VBA: Color range based on cell value

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related