Wildcard in change event ??

  • Thread starter Thread starter Tracey
  • Start date Start date
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?
 
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.
 
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
 
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
 
Back
Top