Excel VBA - conditional formatting

[john.w.saunders]

I have a range from Q16:AJ28 and each cell contains one of five
statuses (LUC, LUD, CIP NL1, CIP NL2, CIP L, IRP). I am trying to
write the VBA code to look through this range and determine if it's
Open1, Selection.Interior.ColorIndex = 37, Open2 =
Selection.Interior.ColorIndex = 33.

I tried making an array of the cell's, but I don't know how to test the
cell contents (an if statements?) An array also seemed a bit overkill.
I also tried doing a For-to loop by offsetting the cells
(Range(currentcell).Offset(0, 1).Address) but once again I couldn't
test the cell contents - however, this does seem like a more logical
approach.

I also tried this code below, but it didn't work for anything with
spaces (or something didn't work..)

I appreciate the help!

Dim icolor As Integer

If Not Intersect(Target, Range("Q16:AJ28")) Is Nothing Then
Select Case Target
Case Is = "LUD"
icolor = 37
Case "LUC"
icolor = 33
Case "CIP NL1"
icolor = 41
Case "CIP NL2"
icolor = 5
Case "CIP L"
icolor = 55
Case "IRP"
icolor = 11
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor
End If


End Sub

 

[Tom Ogilvy]

Sub FormatColor()
Dim icolor As Long
Dim Target as Range

for each target in Range("Q16:AJ28")
Select Case Ucase(Target)
Case "LUD"
icolor = 37
Case "LUC"
icolor = 33
Case "CIP NL1"
icolor = 41
Case "CIP NL2"
icolor = 5
Case "CIP L"
icolor = 55
Case "IRP"
icolor = 11
Case Else
icolor = xlNone
End Select

Target.Interior.ColorIndex = icolor
End If
Next Target
End Sub