Excel search and highlight box

[blewbot]

ok here is what i have... i have a active x label that when clicked opens search box and says enter last name to search schedule...basically i want it to highlight all of those last names in the schedule.

that part works great if it is an exact match, but if there is anything else in the cell it will not highlight the cell.

so my question is how to i adjust this from...

[If MyVal.Value = MySearch Then]

to something more like...

[If MyVal.Value "kinda sorta close to"= MySearch Then]

Thanks for the needed help

Private Sub Label1_Click()
Dim MyVal As Range
Dim MySearch As String
 
MySearch = Application.InputBox("ENTER LAST NAME TO search SCHEDULE", "SEARCH DOWS")
 
    For Each MyVal In Range("C3:O7")
         If MyVal.Value = MySearch Then
            MyVal.Interior.Color = vbYellow
            Else
            MyVal.Interior.Color = vbWhite
            End If
        Next
 
For Each MyVal In Range("C9:O18")
       If MyVal.Value = MySearch Then
            MyVal.Interior.Color = vbYellow
            Else
            MyVal.Interior.Color = vbWhite
            End If
        Next
 
For Each MyVal In Range("C20:O29")
       If MyVal.Value = MySearch Then
            MyVal.Interior.Color = vbYellow
            Else
            MyVal.Interior.Color = vbWhite
            End If
        Next
 
End Sub

 

[blewbot]

BUMP

I could really use help with this, anyone know where i am going wrong... i tired the "like" instead of "=" but it still only return exact matches

 

[Marcelo Branco]

Hi,

Maybe this

       If InStr(1, MyVal.Value, mySearch) Then
            MyVal.Interior.Color = vbYellow
        Else
            MyVal.Interior.Color = vbWhite
        End If

HTH

M.

 

[blewbot]

Thank you, that works!

 

[Marcelo Branco]

Thank you, that works!

You are welcome and tks for the feedback

M.