Adjust the "A" and "B" etc. 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("B2"))
'Set vRngInput = Intersect(Target, Range("B2:B10")) for larger range of cells
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

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste the above into that module.


Gord Dibben MS Excel MVP

On Tue, 16 Jan 2007 13:28:01 -0800, Grd wrote:

Hi everyone,

I need to change the color of a cell based on the value the user types in
the cell.
In this cell B2. If they type ON then it should be blue, BC red, AL yellow,
NS green and PEI pink. I can't use conditional formatting from the menu
because its only got three conditions I could be using many more.

Also I don't know how code can run automatically when a person changes the
value of a cell in a sheet.

Could anyone help

Thanks

Suzanne