Wildcard in change event ??

T

Tracey

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?
 
T

Tracey

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

Peter T

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
 
Top