In the basic Excel “Conditional Formatting” the formatting of a single cell is done based on its value. That way, for example, one can highlight a cell if it contains the value “Highlight”. To do that simply select the needed cell and click on the “Conditional Formatting” option “Highlight Cells Rules” and select the type of rule “Equal To…”, such as the following:
Then type the cell’s rule, in this example the text “Highlight”:
And you are done. The result should look like this:
That is simple enough. But how do you conditionally change the formatting of complete row based on a single cell value? The use case is as follows: One column is representing the status of the row. If that status has the value “Highlight”, the entire row should be formatted to be highlighted.
Well, Excel has an only slightly hidden option to support that scenario. To do that you have to write down a formula to format the rows.
Start by selecting the relevant rows (or a table). Then go to “Conditional Formatting” and select the “New Rule” option:
Then select the last option called “Use a formula to determine which cells to format”:
And then write a rule. In this example, the row should be highlighted if the value of the cell on column A equals to “Highlight”. The rule is:
Like in the picture below:
The weird part of the rule is that it looks like it is only applicable for the first row, but as you can see the rule include $a1 and not $a$1 and there is a big difference.
$a$1 means that the formula will always look at cell A1. Fixed row and column.
However, $a1 (without the dollar sign before the digit 1) means that the column is fixed and will always be column A but the row is variable and will be implemented for all the rows in the selected range.
The end result looks like this:
More examples and guides can be found here: