How can I colour format all cells based on their values

[Guest]

I would be grateful if someone could help me through this process - I need to
colour code numbers 1 to 5. The conditioning statement only allows you 3
chances.

 

[Bob Phillips]

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green

Case 5: .Interior.ColorIndex = 38
End Select
End With
End If


ws_exit:
Application.EnableEvents = True
End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Hi Bob,

After you have done this - how do you use conditional formatting - it still
only allows 3 conditions?

Based on the values of another cell - I have to set the backcolor to one of
five colours.

Thanks

 

[Gord Dibben]

You don't need to use CF if you use Bob's code.

The Select Case.Value looks after the colors.

Which cell(s) do you want colored and which cell(s) are the trigger cell(s)?


Gord Dibben MS Excel MVP