To highlight entire rows with conditional formatting when a value meets specific criteria, use a formula with a mixed reference that locks the column. In the example shown, all rows where the owner is "bob" are highlighted with the following formula applied to B5:E12:
Note: CF formulas are entered relative to the "active cell" in the selection, B5 in this case.
How this formula works
When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the address of the active cell (B5) is used for the row (5) and entered as a mixed address, with column D locked and the row left relative. When the rule is evaluated for each of the 40 cells in B5:E12, the row will change, but the column will not.
Effectively, this causes the rule to ignore values in columns B, C, and E and only test values in column D. When the value in column D for in a given row is "Bob", the rule will return TRUE for all cells in that row and formatting will be applied to the entire row.
Using other cells as inputs
Note that you don't have to hard-code any values that might change into the rule. Instead you can use another cell as an "input" cell to hold the value so that you can easily change it later. For example, in this case, you could put "Bob" into cell D2 and then rewrite the formula like so:
You can then change D2 to any priority you like, and the conditional formatting rule will respond instantly. Just make sure you use an absolute address to keep the input cell address from changing.
Named ranges for a cleaner syntax
Another way to lock references is is to use named ranges, since named ranges are automatically absolute. For example, if you name D2 "owner", you can rewrite the formula with a cleaner syntax as follows:
This makes the formula easier to read and understand.
Excel contains a built-in preset for highlighting duplicate values with conditional formatting, but it only works at the cell level. If you want to highlight entire rows that are duplicates you'll need to use your own formula, as explained below. If...
To highlight intersecting row(s) and column(s) with conditional formatting based on exact matching, you can use a simple formula based on mixed references and the OR function. In the example shown, the formula used to apply conditional formatting is...
In this Exceljet tip, we show you how to highlight entire rows in a list using conditional formatting with a formula. This is a handy way to visually expose information you are interested in without having to apply a filter. We also show you how to...
In this video, we use conditional formatting to create an interactive search box to highlight data. This is a nice alternative to filtering, because the information you're looking is highlighted in context, and the search box checks multiple...
In this video, we look at how to make conditional formatting variable, so that you can change how formatting is applied without editing a rule. This elegant technique helps you build killer spreadsheets that are fun to use.
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.