"JonnyCrabb" wrote in message
...
Hi Bob,

Thanks for your reply. I am a friend of Rob's who is helping him out with
this.

No problems, anyone can join in.

Just to understand your code, I presume H1:H10 is the range of values that
you are considering, and the "value 1", "value 2" etc are the values that
you
are looking for in that range. Hence the "do something" is what you want
doing when a value in your range equals one of your spefied values.

Correct on all points.

What we are trying to achieve is to say if a value in a range is "red",
then
that cell turns red. If the value is "orange", the cell turns orange.


I thought I did this self same thing a few days ago but I can't find it.
As such the "do something" code would look something like:
.ColorIndex = 6
.Pattern = xlSolid
but I don't know how to refer to the cell that contains the value to
change
the formatting as appropriate. Can you (or anyone else) help?

Option Explicit

Private Const xlCIBlack As Long = 1
Private Const xlCIWhite As Long = 2
Private Const xlCIRed As Long = 3
Private Const xlCIBrightGreen As Long = 4
Private Const xlCIBlue As Long = 5
Private Const xlCIYellow As Long = 6
Private Const xlCIPink As Long = 7
Private Const xlCITurquoise As Long = 8
Private Const xlCIDarkRed As Long = 9
Private Const xlCIGreen As Long = 10
Private Const xlCIDarkBlue As Long = 11
Private Const xlCIDarkYellow As Long = 12
Private Const xlCIViolet As Long = 13
Private Const xlCITeal As Long = 14
Private Const xlCIGray25 As Long = 15
Private Const xlCIGray40 As Long = 16
Private Const xlCIPaleBlue As Long = 17
Private Const xlCIPlum As Long = 18
Private Const xlCILightTurquoise As Long = 20
Private Const xlCILightBlue As Long = 23
Private Const xlCIBrown As Long = 30
Private Const xlCISkyBlue As Long = 33
Private Const xlCILightGreen As Long = 35
Private Const xlCILightYellow As Long = 36
Private Const xlCILavender As Long = 39
Private Const xlCIAqua As Long = 42
Private Const xlCILime As Long = 43
Private Const xlCIGold As Long = 44
Private Const xlCILightOrange As Long = 45
Private Const xlCIOrange As Long = 46

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then
With Target
Select Case LCase(.Value)
Case "red": .Interior.ColorIndex = xlCIRed
Case "blue": .Interior.ColorIndex = xlCIBlue
Case "yellow": .Interior.ColorIndex = xlCIYellow
'etc
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub