Conditional Format Question

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hi All

Please could someone let me know if this is possible, as I can't find a
definitive answer

In one column the cells will have random numbers from 1 to 5

I would like all 1's coloured green, all 2's, coloured blue etc etc, I can
get this to 3 by adding another condition but then it doesn't let me add
anymore conditions

Thanks for any help

Cheers

Steve
 
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
Application.EnableEvents = False
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
 
Steve

You will need VBA to accomplish this.

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
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = 1: Num = 6 'yellow
Case Is = 2: Num = 10 'green
Case Is = 3: Num = 5 'blue
Case Is = 4: Num = 3 'red
Case Is = 5: Num = 46 'orange
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
End Sub
 
Back
Top