I've managed to do it with:
Sub test()
Dim LR As Long, i As Long, c As Long
LR = Range("E" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = 1 To LR
With Range("E" & i)
Select Case .Value
Case "CP": c = 35
Case "LG": c = 37
Case Else: c = xlNone
End Select
.Offset(, -4).Resize(, 20).Interior.ColorIndex = c
End With
Next i
Application.ScreenUpdating = True
End Sub
However I would like to have the rows alternate between the color index and white and also avoid formatting the headings row as default which is in row 2. Any ideas?
Bookmarks