Highlight Rows that Meet Criteria in Searchbox in Excel

In this article, we will learn how to highlight rows that meet criteria. This technique will be based on Conditional Formatting & regular formulas.

The formulas we are going to use are IF, ISBLANK, ISNUMBER, SEARCH together to get the output.

 

 

Let us take an example:

We have Product data wherein the text or number entered in the searchbox will trigger the formula to highlight the entire rows.

img1

The objective is to highlight the entire rows which match value in cell B2.

We need to follow these steps:

  • Select the range A5:D10
  • Click on Home tab
  • From Styles group select Conditional Formatting
  • Click on New Rule

img2

  • In the New Formatting Rule dialogue box select “Use a formula to determine which cells to format”

img3

  • Enter the formula =IF(ISBLANK($B$2),0,ISNUMBER(SEARCH($B$2,$A5&$B5&$C5&$D5)))

img4

  • Click on the Format & set the formatting, then click Ok twice.

 

The formula in conditional formatting will highlight every row that matches value in cell B2

If we enter Paper in cell B2; we get:

img5

If we enter text from SKU column; we get:

img6

In this way, we can highlight the entire rows if the text is found in any of the data table.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube