How To Set Conditional Formatting To Highlight An Entire Record Row Based On Value In One Field

Let's say you have started a side business. You sell about 21 different products. You've hired a trusted hand to manage the business but he is not Excel savvy. So you decided to set up some basic templates to handle invoicing, accounting and inventory management.

We will focus on the inventory management template you set up. It documents the stock of the different products you have -- the quantity in stock, the cost price and number of days they can be stored for. There is just one more thing you want to add. You want the template to highlight in orange products that are due for restocking. And your restocking rule is to not have less than 40 of any product. So once a product quantity drops below 40, it is marked for restocking.

How do you do that with Excel? 

You use conditional formatting. And in a creative way because you want it to not only highlight the quantities that are less than 40 but the entire record line for the products with quantities less than 40.

Form this:

To this:

And here are the steps to achieving it.

Highlight the entire records without the header. Go to Home menu, conditional formatting and click on new rule.

Select "Use a formula .." in the formatting rule dialog box.
In the formula box that shows, press = and select the first record in the quantity row. It will come as $B$3 (a dollar sign before the column alphabet and the row number). In our case we don't want the formula to drag to other columns (like cost, storage days and product columns) so you will leave the dollar sign before the column alphabet. But we do want it to drag downwards to other rows, so it would do each row in our table; for that we remove the dollar sign in front of the row number.

Final formula will look like this: =$B3 less than 40<40 40="" font="">
Got it?

Then you set the formatting to be displayed.

And that is all! Click on OK.

You should now see the result.

And that is how you set conditional formatting to highlight an entire row based on value in just one field.