Private Sub Worksheet_Change(ByVal Target As Range)
Dim colour As Long
Dim pos As Long
Dim suffix As String
Dim sVal As String

On errro GoTo errExit
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
sVal = UCase(CStr(Target(1)))
pos = InStrRev(sVal, " ")
colour = xlNone

If pos Then

suffix = Mid$(sVal, pos + 1, Len(sVal) - pos)

Select Case suffix
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

End If

With Target(1).Interior
If .ColorIndex < colour Then
.ColorIndex = colour
End If
End With
End If
errExit:
End Sub

Regards,
Peter T

"Tracey" wrote in message
...
Yes, I will - bearing in mind that the cell result will be "First Surname
HP"
--
Tracey @ BrisVegas


"Peter T" wrote:

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

.



.