highlight a row if meets criteria

[ghynes]

can anyone tell me how to highlight a row in orange if a certain cells meets certain criteria?

every day i get a table of data and i want to be check every row and highlight
the row if C1 > 10.
the number of rows change every day and the number of columns change. thanks

 

[VoG]

Press ALT + F11 to open the Visual Basic Editor, Insert > Module and paste in the code.

Press ALT + F11 to return to the sheet, Tools > Macro > Macros, highlight orange and click the Run button.

 

[ghynes]

Try

Sub orange()
Dim LR As Long, i As Long
LR = Range("C" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("C" & i)
        If .Value > 10 Then .EntireRow.Interior.ColorIndex = 45
    End With
Next i
End Sub

thanks. i have it working now. but i dont want to highlight the entire row.. just from A to F

 

[VoG]

Try this

Sub orange()
Dim LR As Long, i As Long
LR = Range("C" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If IsNumeric(Range("C" & i).Value) And Range("C" & i).Value > 10 Then Range("A" & i & ":F" & i).Interior.ColorIndex = 45
Next i
End Sub

 

[ghynes]

brilliant! thanks all

 

[Peter_SSs]

ghynes

Your original post said that the number of rows and number of columns changes every day. This code has now been fixed at columns A:F, so what happens if the number of columns change with the next lot of data?

Also, with the rows changing, I don't know if it is possible that the number of rows may be decreasing or if the data in existing rows can change from day-to-day? For example. if row 100 today is orange because C100 > 10 and tomorrow there is less than 100 rows or the value in C100 tomorrow is less than 10, then the row will still be orange when it shouldn't be.

Perhaps your requirements have changed (or I mis-interpreted them in the first place) otherwise you might consider modifying the code to remove existing colour (and possibly determine the number of columns) before applying colour the next time. Another option is to try the code I suggested in post #8.