I have a sheet with a list of items. I want to create VBA to bring up a search box that asks them what to search for and then either list all of the items and their cell reference OR (preferably), highlight the items in the list.
I found the following code on another site, but it only lists the first instance that it finds AND I don't like the way it lists it in the same search box. I would prefer that it highlight within the list, jump to the location, or at a minimum, show a message box with the location of the items.
Sub FindSomething()
Dim Prompt As String
Dim RetValue As String
Dim Rng As Range
Dim RowCrnt As Long
With Sheets("Sheet1")
Do While True
RetValue = InputBox(Prompt & "Enter item to find:")
'RetValue will be empty if you click cancel
If RetValue = "" Then
Exit Do
End If
Set Rng = .Columns("C:C").Find(What:=RetValue, After:=.Range("C8"), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Rng Is Nothing Then
Prompt = "I could not find """ & RetValue & """"
Else
RowCrnt = Rng.Row
Prompt = "I found """ & RetValue & """ on row " & RowCrnt
End If
Prompt = Prompt & vbLf
Loop
End With
End Sub
Any help is appreciated.
Bookmarks