Search with conditional formatting

Searching for rows containing a particular word or text
can be easily accomplished with the auto-filter function.
Just type the word into the box found within the auto-filter
dropdown list (for Excel 2013 and above) and auto-filter
will display those rows that contain the word or text found
in that particular column. What if the word
you wish to search for can be found in multiple columns? In this
scenario, auto-filter will not work. One of the solutions to
this problem is to apply conditional formatting to the whole
range of data, just like how we do it below.

First, fill a cell with a colour and name
the cell "Search_box". This will make it easier to use later
in a formula. Then add some text, so you can see the rule
applied once it's created.

When you have completed the steps, you will get the same
as shown in the picture below.

Now we need to add a rule that uses the search box. Select
the entire data range, and add a custom conditional
formatting rule that uses a formula.

To make the rule flexible, we are going to use the SEARCH
function. SEARCH takes 3 arguments: the text to search for,
the text to look within, and, optionally, a starting
position. When SEARCH finds something, it returns the
position as a number. If the text is not found, it returns
zero.

Here is how it is done.

First select the range that you want to
format conditionally. In our example above select the whole
range from A4:H25.

Go to the Home tab and select
conditional formatting (for Excel 2007 – 2016). Conditional
Formatting is grouped within Style.