Here is how we will use Conditional Formatting to highlight the entire row in a data range based on the values in one column:
(Note: Video version above, text version below)
Text version (note: click on an image for a better view):
Here I have a data range of 214 rows. I want to highlight the entire row for anyone that has USA in column A.
I need to highlight the entire range starting in cell A2 through E214. Then I will click on the Conditional Formatting button in the Styles group on the Home tab and select New Rule:
I will then select “Use a formula to determine which cells to format”. The formula I will enter is =$A2=”USA”. Notice how I have a mixed cell reference $A2. As the data range is being evaluated for formatting, I want to lock Column A since that is where I want it to check for “USA”, but go down each row. I also put “USA” in quotes since it is text. I then click on the “Format” button and format the cells how I want them to appear:
I then hit OK and OK, and now my data range is formatted perfectly. If I change any of the entries in column A, the formatting will adjust accordingly.