More than 3 Conditional Formats

[teknovision]

Hi all!

Conditional formatting is super however, it only allows for thre
conditional formats! What to do when you have five?

When a user types in BLUE (from a drop down list). I would like th
background/shading to go blue and so on for RED, GREEN, AMBER an
PURPLE.

Any ideas would be very welcome? Many thanks in advance for any hel
you can offer.. .

Phi

 

[Frank Kabel]

Hi
conditional format only accepts 3 conditions though you have a fourth
if you include the default format.

If you only want to apply different FONT colors based on NUMBERS, you
can define up to 6 different styles. See:
http://www.mcgimpsey.com/excel/conditional6.html
for instructions how to do it

For everything else you'll need VBA code (e.g. process the
worksheet_change event and apply your format based on the cell values).
The following will color the entry in cell A1:A100 based on its value:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
Select Case .Value
Case "Red": .Interior.ColorIndex = 3
Case "Blue": .Interior.ColorIndex = 10
'etc.
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub

 

[Bob Phillips]

Phil,

You can do it with VBA.

This is the sort of code you need as worksheet event code, which is
worksheet event code Change A20 to your range.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A20")) Is Nothing Then
Select Case Target.Value
Case 1: Target.Interior.ColorIndex = 1
Case 2: Target.Interior.ColorIndex = 2
Case 3: Target.Interior.ColorIndex = 3
Case 4: Target.Interior.ColorIndex = 4
Case 5: Target.Interior.ColorIndex = 5
Case 6: Target.Interior.ColorIndex = 6
End Select
End If

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)