code to look at the result of "Tracey HP"

Are you only looking at the characters after the last space, if there is one

Regards,
Peter T

"Tracey" wrote in message
...
Cell A1 in sheet 2 has a formula of:-
Sheet1!A1&" "&Sheet1!B1 giving a result of "Tracey HP"

The event code is currently:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim colour As Integer

If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
Select Case Target
Case Is = "HP"
colour = 41
Case Is = "PNL"
colour = 10
Case Is = "PCL"
colour = 35
Case Is = "HV"
colour = 6
Case Is = "PM"
colour = 3
Case Is = "TSTC"
colour = 2
Case Else
'do nothing
End Select

Target.Interior.ColorIndex = colour
End If

End Sub

This is looking for exact matches but, if possible I would like the event
code to look at the result of "Tracey HP" and trigger the colour change
because it has found a 'like' result of "HP".

My final sheet will have approx 30-50 lines of data with different names
and
codes.

--
Tracey @ BrisVegas


"Don Guillett" wrote:

examples and your code?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tracey" wrote in message
...
Is it possible to use a wildcard in change event code

Case Is = "HP"
colour = 41

Can the Case Is be used to look for instances of "HP" amongst other
text
like a concatenate formula?
--
Tracey @ BrisVegas

.