| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wildcard in change event ??
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Wildcard in change event ??
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Wildcard in change event ??
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 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Wildcard in change event ??
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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Wildcard in change event ??
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 . . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Wildcard in change event ??
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 . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| apply cell change event to single column - WorksheetChange Event | Excel Programming | |||
| How do I change a Worksheet_change event to a beforesave event? | Excel Programming | |||
| MsgBox in Enter event causes combobox not to run Change event | Excel Programming | |||
| Change event and calculate event | Excel Programming | |||
| change event/after update event?? | Excel Programming | |||
Hybrid Mode
