Highlight Row Base on A Cell Value in Excel

Earlier we learned how to highlight entire if any cell in row contains specific text. In this article, we will learn how to highlight row if cell in a contains a specific value. Not only text, if any criteria is met, we will highlight the row using conditional formating.

Generic Formula in Conditional Formating

Cell with relative row: this is the a cell reference where column is absolute and row is relative. For example $A1. Learn in detail about referencing in excel here.

Criteria: It is the criteria that you want to match and highlight row. It can be a text a value or logical test.
Let’s see an example to make things clear.
Example: Highlight Row Based on Cell Value of Rates
Here I have some data of fruits. I just want to highlight entire row if fruit rate is greater then 100.

Select first row of table (A2:C2).

1. Go to conditional formatting and click on new rule. You can use sequential shortcut ALT>H>L>N.
2. Select “use a formula to determine which cell to format”.
3. Write this formula.
=$C2>100

It is necessary to select the entire row before doing conditional formatting.

4. Click on format and choose yellow in fill. Hit ok.

Copy this range and paste special the format to entire table.

Now each row will be highlighted whose rate is greater than 100. Here I have hardcoded the criteria, but you can give cell reference too.

How it works?

Well, it is quite simple.  We simply check if value in $C2 is greater than 100 or not. If it is, we tell excel to highlight the entire row.

Note that only Column C is frozen using $ and row are left relative. It is because we want to change the lookup row when we copy the conditional formatting and while column stands absolute. In the end we get highlighted rows that contains value greater than 100. Learn about referencing in detail here.
So yeah guys, this how you can highlight entire row based on cell value or criteria. This is quite easy if you understand absolute and relative referencing in excel. Let me know if you have any doubts or queries regarding this article or any other topic of excel/VBA. The comments section is all yours.
 

 
Popular Articles:

The VLOOKUP Function in Excel
COUNTIF in Excel 2016
How to Use SUMIF Function in Excel