Despite the Find and Replace Tool used to find and locate specific values, do you know you can highlight the search result? Conditional Formatting is a tool used in Excel to make cells or part of the worksheet easily visible. If you are working on a large dataset, you must know how to use this Tool. A conditional Formatting tool is used to search and highlight the search results. In this article, we shall discuss ways of using this Tool in Excel.
To Search and Highlight Data using the Conditional Formatting feature
Here are the steps to follow:
1. Open the Excel application.
2. Open the existing document that contains the dataset you wish to search for data. Then, create a helper cell containing the value you want to search for.
3. Highlight the dataset on which you wish to search data or apply the conditional Formatting.
4. Click on the Home tab on the Ribbon, and locate the Styles section.
5. Click the Conditional Formatting drop-down button. From the drop-down menu, click the New Rule button.
6. A New Formatting Rule dialogue box will open. In the dialogue box, choose the "Use a formula to determine which cell to format" option.
7. In the "Format values where this formula is true:" section, type the following formula: =A2=$E$2
8. Click on the Format button. In the Format Cells dialogue box, click on the Fill tab. Choose the background color you want to apply from the Background Color.
9. Click OK to save the changes. That is all you need to do.
To Search and Highlight Data using VBA Code
Here are the steps to follow:
1. Open the Excel application.
2. Open the existing document that contains the dataset you wish to search for data.
3. Highlight the dataset on which you wish to search data or apply the conditional Formatting.
4. Click on the Developer tab on the Ribbon, and then locate the Visual Basic button. Alternatively, press the Alt + F11 keys on your keyboard to open the Visual Basic feature.
5. In the Visual Basic screen, click the Insert tab on the Ribbon and select the Module button.
6. Type the following code in the empty module.
Sub FindRange()
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
7. While on the Visual Basic screen, press the F5 key to run the code. Alternatively, click on the Run tab on the toolbar. From the menu, click the Run sub/UserForm button to run the code.
8. That's all.
