Highlight All Cells Containing a Specific Word in an Excel Range (VBA)

Below is a demonstration of a VBA macro that helps to select the cells in a range containing the string indicated in a reference cell.

• 

The set up is in 4 basic steps:

• Identify the range you are searching for the string instance in, and name it (optional). In the example above, this range is A3:B15. I have named it Range_to_Search for ease of reference in the VBA.

• 

• Set up a search box cell. This is the cell we will input the string we want to search for within the range. This is $C$3 in our example above. I have also named this, Search_Cell, for ease of reference.

• 

Navigate to the Visual Basic Editor (File > Developer > Visual Basic) and create a new module (Insert>Module). Place the below code in the module:

Sub Findname()
For Each cell In Range("Range_to_Search")
If myRng = Empty And cell = Range("Search_Cell") Then myRng = cell.Address(0, 0) ElseIf cell = Range("Search_Cell") Then myRng = myRng & "," & cell.Address(0, 0) End If
Next cell
If myRng = Empty Then Exit Sub
Range(myRng).Select
End Sub

• 

Create a shape, and make the shape an action button by assigning the new macro you just created to this button. You will see the option to 'Assign macro' when you right-click the shape. This step is optional: you can choose to run the code directly by clicking the Macro/Run Macro icon on the Developer tab (or using the keyboard shortcut Alt+F8 in Windows) and selecting the macro name.

• 

And it's all set!