Search column based on input box entry - all cells

[Smiley54665]

Hi,
I am fairly comfortable with VBA, but definitely still consider myself a beginner. I need some help with coming up with VBA module to accomplish the following:

I have labeled data columns in A1:F345. I need my users to be able to click on a box to launch the macro and enter a search word into an input box and then the macro search for any/all cells in column F that contains that value. I would want this to find the text in the cell even if the entire contents do not match.

If possible, I would like to avoid the use of the Ctrl + F functionality of excel because it seems to be working only intermittently with my sheet even if conduct the search manually.

I would then like each of the cells that contain that string to change their background color to yellow.

Any help would be greatly appreciated. Thank you!

 

[tlowry]

Here is some code that will "yellow" cells that contain the entered text. Just run it, and enter the search text.

I'm not sure what

click on a box

means. If it means a button then assign this macro to the button.

To install, copy this to a module.

Sub FindIt()
    Const tSheet = "Sheet1"
    Const rRng = "A1:F345"
    Const Yellow = 6
    Const NoColor = xlNone
    Dim c, firstaddress, sResp
    
    sResp = InputBox("What are you looking for?", "Finder")
    If sResp = "" Then Exit Sub
    
    With Sheets(tSheet).Range(rRng)
        .Interior.ColorIndex = NoColor
        Set c = .Find(sResp, LookIn:=xlValues)
        If Not c Is Nothing Then
            firstaddress = c.Address
            Do
                c.Interior.ColorIndex = Yellow
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstaddress
        End If
    End With
    
End Sub

 

[Smiley54665]

Yes, by "click on a box", I was referring to assigning a button for the macro. Sorry for the confusion.

This works perfectly! Thank you very much!