Highlight Search Result

[JPRMA]

Hello,
I have found in one of your threads the following vba code to search a worksheet using an input box. It works great.
I was not wandering how to highlight in yellow the search result.

Thank you

Sub Search()
    Dim fnd         As String
    Dim FoundCell   As Range
    Dim Ws          As Worksheet
   
    Do
        fnd = InputBox("Enter the SSN or any part of text you want to search")
        'cancel pressed
        If StrPtr(fnd) = 0 Then Exit Sub
    Loop Until Len(fnd) > 0
   
    For Each Ws In Worksheets
        Set FoundCell = Ws.UsedRange.Find(fnd, , xlValues, xlPart, , , False, , False)
      
             
        If Not FoundCell Is Nothing Then
            Ws.Activate
            FoundCell.Select
           
          
            Exit Sub
        End If
       
    Next Ws


End Sub

 

[LazyBug]

deleted

 

[JPRMA]

Hello. Thank you for your prompt reply.
However getting the following error message:

Object variable or With block variable not set

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Try this

Sub Search_v2()
    Dim fnd         As String
    Dim FoundCell   As Range
    Dim Ws          As Worksheet
   
    Do
        fnd = InputBox("Enter the SSN or any part of text you want to search")
        'cancel pressed
        If StrPtr(fnd) = 0 Then Exit Sub
    Loop Until Len(fnd) > 0
   
    For Each Ws In Worksheets
        Set FoundCell = Ws.UsedRange.Find(fnd, , xlValues, xlPart, , , False, , False)
      
        If Not FoundCell Is Nothing Then
            Ws.Activate
            FoundCell.Interior.Color = vbYellow
            Exit Sub
        End If
       
    Next Ws
End Sub

 

[JPRMA]

It works great. Thank you

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.