Home » Questions » Computers [ Ask a new question ]

Highlighting search results in Excel

Highlighting search results in Excel

Is it possible to have Excel highlight search results, or specific text that you're looking for? I really appreciate these features as they are in Firefox and IntelliJ IDEA.

Asked by: Guest | Views: 252
Total answers/comments: 1
Guest [Entry]

"I googled your demand and found out that you can use a macro to do this.

Sub FindHighlight()
Dim tempCell As Range, Found As Range, sTxt, FoundRange, Response As Integer
Set Found = Range(""A1"")
sTxt = InputBox(prompt:=""Enter value for search"", Title:=""VoG's Finder"")
If sTxt = """" Then Exit Sub
Set tempCell = Cells.Find(what:=sTxt, After:=Found)
If tempCell Is Nothing Then
MsgBox prompt:=""Not found"", Title:=""VoG's Finder""
Exit Sub
Else
Set Found = tempCell
Set FoundRange = Found
End If
Do
Set tempCell = Cells.FindNext(After:=Found)
If Found.Row >= tempCell.Row And Found.Column >= tempCell.Column Then Exit Do
Set Found = tempCell
Set FoundRange = Application.Union(FoundRange, Found)
Loop
FoundRange.Interior.ColorIndex = 6
Response = MsgBox(prompt:=""Clear highlighting"", Title:=""VoG's Finder"", Buttons:=vbOKCancel + vbQuestion)
If Response = vbOK Then FoundRange.Interior.ColorIndex = xlNone
End Sub

When the macro runs it will highlight
the found cells and give the option to
clear the highlighting. If you want to
temporarily keep the highlighting
click Cancel. Then, to clear the
formatting later on, run the same
search and click OK.

Click here to view the source."