I'd try replacing Select Case rng.Value with Select Case rng.Text
--
HTH,
Barb Reinhardt



"Madduck" wrote:

Team,

I have found this post by Gord Dibben and can get it to work using numbers
as the possible results
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("A1"))
' for a range use "A1:A10" or similar
On Error GoTo endit
Application.EnableEvents = False
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
endit:
Application.EnableEvents = True

End Sub


But I really want it to work for Text based events... nothing I do seems to
motivate the change though...


i.e
Case Is = "Martin": Num = 6 'yellow does not work
Case Is = Martin: Num = 6 'yellow does not work

Would it be because my cell is a formula ?

=VLOOKUP($B7,'CRT for FTE'!$A$7:$C$43,3,FALSE)

Thanks in advance..