Gronk_h8er
Board Regular
- Joined
- Jan 6, 2009
- Messages
- 63
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.
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.