Gra

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("C9:C13"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo enditall
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = 1: Num = 3 'red
Case Is = 2: Num = 46 'orange
Case Is = 3: Num = 6 'yellow
Case Is = 4: Num = 10 'green
Case Is = 5: Num = 15 '25% gray
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
enditall:
Application.EnableEvents = True
End Sub


This is sheet event code.

Right-click on the sheet tab and "View Code"

Copy/paste the above into that module.

Don't know what index number for Amber so gave you Orange.


Gord Dibben MS Excel MVP

On Thu, 13 Jul 2006 16:11:55 +0100, Gra wrote:


Hi, apologies if this has been answered on a previous thread, but I've
read through many of them and as I'm new to VBA I don't seem to be
getting anywhere!

My problem is the age-old one of needing more than three conditional
format values.

In column A I have a series of statements. In column B, users of this
sheet are asked to answer these statements using a data validated pick
list of 5 choices (the source for which is a list in cells C9:C13). I
need to create some VBA that will colour the font of choice 1 in red,
choice 2 in amber, choice 3 in yellow, choice 4 in green and choice 5
in grey.

Any assistance would be gratefully received!