"Conditinal Format" question?

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hi everyone,

Say you want to do mapping using colors. Conditional Format allows you
to use up to 3 colors (3 conditions)!

Assuming you have more 6 conditions to color-map. Can you still use
Conditional Format here? If so, how?

Thanks,
Mike
 
Mike

If your data are numbers, JE McGimpsey's site has a method for getting up to 6
CF.

You can also use sheet event code to allow many colors.

Example code.....adjust to suit.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim Rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("D:D"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each Rng In vRngInput
'Determine the color
Select Case Rng.Value
Case Is = "A": Num = 10 'green
Case Is = "B": Num = 1 'black
Case Is = "C": Num = 5 'blue
Case Is = "D": Num = 7 'magenta
Case Is = "E": Num = 46 'orange
Case Is = "F": Num = 3 'red
End Select
'Apply the color
Rng.Interior.ColorIndex = Num
Next Rng
endit:
Application.EnableEvents = True
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim Rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each Rng In vRngInput
'Determine the color
Select Case Rng.Value
Case Is <= 0: Num = 10 'green
Case 0 To 5: Num = 1 'black
Case 5 To 10: Num = 5 'blue
Case 10 To 15: Num = 7 'magenta
Case 15 To 20: Num = 46 'orange
Case Is > 20: Num = 3 'red
End Select
'Apply the color
Rng.Font.ColorIndex = Num
Next Rng
endit:
Application.EnableEvents = True
End Sub


Gord Dibben Excel MVP
 
Back
Top