Search and highlight search results in Excel
In Excel, you can use the Find and Replace function to find a specific value, but do you know how to highlight the search results after searching? In this article, I introduce two different ways to help you search and highlight search results at the meanwhile in Excel.
|Quickly Insert Bubble/Speedometer/Bell Curve Chart |
|The Bubble chart, speedometer chart and bell curve chart is usually used for analyze data in Excel, but the creation of all of them is complex in Excel. Here, Kutools for Excel's Charts group can help you quickly create the Bubble chart, Speedometer chart or Bell Curve chart. |
Click for 30-day free trial.
In Excel, there is a VBA code that can help you search a value and then highlight the search results.
1. Enable the sheet you want to search and highlight results, press Alt + F11 keys to open Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste below code to the new module.
VBA: Highlight search results
Sub FindRange() 'UpdatebyExtendoffice20190813 Dim xRg As Range Dim xFRg As Range Dim xStrAddress As String Dim xVrt As Variant xVrt = Application.InputBox(prompt:="Search:", Title:="www.extendoffice.com") If xVrt <> "" Then Set xFRg = ActiveSheet.Cells.Find(what:=xVrt) If xFRg Is Nothing Then MsgBox prompt:="Cannot find this value", Title:="www.extendoffice.com" Exit Sub End If xStrAddress = xFRg.Address Set xRg = xFRg Do Set xFRg = ActiveSheet.Cells.FindNext(After:=xFRg) Set xRg = Application.Union(xRg, xFRg) Loop Until xFRg.Address = xStrAddress If xRg.Count > 0 Then xRg.Interior.ColorIndex = 8 xRsp = MsgBox(prompt:="Do you want to cancel highlighting?", Title:="www.extendoffice.com", Buttons:=vbQuestion + vbOKCancel) If xRsp = vbOK Then xRg.Interior.ColorIndex = xlNone End If End If End Sub
3. Press F5 key, then a searching box pops out for you to type the specified value you search.
4. Click OK, the matched results have been highlighted with a background color. Also, a dialog pops out to ask you if cancel the highlighting. Click OK to cancel the highlighting and close the dialog, click Cancel to keep the highlighting and close the dialog.
1. If there is no matched value found, a dialog pops out to remind you.
2. This VBA works for the whole active sheet and case insensitive.
Do You Want To Have A Pay Raise and Much Time To Accompany With Family?
Office Tab Enhances Your Efficiency By 50% In Microsoft Office Working Right Now
Unbelievable, working at two or more documents is easier and faster than working at one.
Compareed with well-known browsers, the tabbed tool in Office Tab is more powerful and more efficient.
Reduce hundreds of mouse-clicks and keyboard typing every day for you, say goodbye to the mouse hand now.
If you usually work at multiple documents, Office Tab will be a great time-saver for you.
30-day free trial, no credit card required.
In Excel, the Conditional Formatting function also can automatically highlight the search results.
Supposing the data and search box display as below screenshot, now please do as below steps:
1. Select the range you want to search in, and click Home > Conditional Formatting > New Rule.
2. In the New Formatting Rule dialog, select Use a formula to determine which cells to format in the Select a Rule Type section, then go to the text box under Format values where this formula is true, type =AND($E$2<>"",$E$2=A4).
E2 is the cell you will place the search value, and A4 is the first cell in the range where you search in.
3. Click Format button, go to the Format Cells dialog, under Fill tab, choose one color as you need. Click OK > OK to close the dialogs.
From now on, when you enter the keyword in Cell E2, the search results will be automatically highlighted in the specified range.
Click to download the sample file
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 1 months agoHi thanks for your help but when we click ok for cancel highlighting. color of cell change. I want orignal color after cancel. What code i need to change in VBA.