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..

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..

Thanks Barb,

I tried that , but funny* things are happening and I don't think it
works.... Should the Text be in " " ? ie :

Case Is = "Martin": Num = 6 'yellow or
Case Is = Martin: Num = 6 'yellow

either way it still does not seem to do the job.. ( nothing happens )

Also doesnt matter if I just type Martin in the cell..(as opposed to the
formula)

*Funny things I am referring to is that the VB stopped working on the .value
and numbers... I had to restart Excel for it to kick in again...

Can you confirm, I thought the trigger event for the VB to change was a
change in value of any cell within the nominated range? Am I wrong?

Thanks again.
Mark.

"Barb Reinhardt" wrote:

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..

I've got a long extraction macro running so can't easily check this in the
VBE.

Is there some reason that you do this

Dim vRngInput As Variant

I think I'd use

Dim vRngInput As Range
--
HTH,
Barb Reinhardt



"Madduck" wrote:

Thanks Barb,

I tried that , but funny* things are happening and I don't think it
works.... Should the Text be in " " ? ie :

Case Is = "Martin": Num = 6 'yellow or
Case Is = Martin: Num = 6 'yellow

either way it still does not seem to do the job.. ( nothing happens )

Also doesnt matter if I just type Martin in the cell..(as opposed to the
formula)

*Funny things I am referring to is that the VB stopped working on the .value
and numbers... I had to restart Excel for it to kick in again...

Can you confirm, I thought the trigger event for the VB to change was a
change in value of any cell within the nominated range? Am I wrong?

Thanks again.
Mark.

"Barb Reinhardt" wrote:

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..