Conditional Formatting Macro

[Gronk_h8er]

Hi,

I am trying to put together a heat map in excel 2003 and need to have more than 3 conditions. Therefore i need to use VBA.

For my range of cells F2:AL200, i need all cells that contain "U" to be red, all cells that contain "S" to be bright green and all cells that contain "" to be grey.

I have downloaded a macro from the web and tried to adjust it for my needs, but it doesn't seem to be working.

Please find it below...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

If Not Intersect(Target, Range("F2:AL200")) Is Nothing Then
Select Case Target
Case Is = "U"
icolor = 3
Case Is = "S"
icolor = 4
Case Is = ""
icolor = 15
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor
End If

I am not proficient enough at VBA to figure out were i am going wrong so any help will be appreciated.

Also, while i am here...On a another sheet i want to create a similar heat map except now the conditions are numbers.

I need all numbers greater than 1 to be dark blue, all numbers that equal 1 to be light blue, all numbers that equal 0 to be green, all numbers that are equal to -1 to be orange and all numbers less than -1 to be red.

Thanks.

 

[Gronk_h8er]

Thanks so much Stan!

Works perfectly!

I really appreciate the level of effort people will put in for complete strangers just to help them out! This is a really great community!

Cheers.