Color cells based on values

[Bob Phillips]

I tried conditional formatting but that didn't work because it's only applicable for those rows that start with "Rep:". So I need VBA to find these rows in my report (it's a dynamic report) and then set the conditional formatting for all columns from F until the end of the report.

Any suggestions?

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim LastCol As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To LastRow

LastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column

With .Cells(i, "F").Resize(, LastCol - 5)
.FormatConditions.Delete
.FormatConditions.Add _
Type:=xlExpression, _
Formula1:="=LEFT($A" & i & ",4)=""Rep:"""
.FormatConditions(1).Interior.ColorIndex = 3 'Red
End With
Next i

End With

End Sub
[/vba]