Built-in conditional formatting colours only the cell that meets the rule. Usually you want the whole row to light up: the full invoice line that’s overdue, not just its status cell. A formula rule does it.
In this article
The one rule that matters: lock the column, not the row
Select the full table, say A2:J201 (start from row 2). Home › Conditional Formatting › New Rule › Use a formula:
=$H2="Overdue"$H keeps every cell looking at column H; the unlocked 2 lets each row check its own row. Always write the formula for the first row of your selection.
Useful row rules
=$I2>50000 ' big orders
=AND($G2<TODAY(), $H2<>"Paid") ' due date passed and unpaid
=$G2-TODAY()<=7 ' due within a week
=$D2=$L$1 ' matches the customer typed in L1 (search highlight)
=COUNTIFS($D$2:$D$201,$D2,$A$2:$A$201,$A2)>1 ' duplicate customer + date
=ISEVEN(ROW()) ' banded rowsRules are checked top to bottom in Manage Rules. Tick Stop If True when a red “overdue” rule must win over a yellow “due soon” rule.
Banding that survives filtering
ISEVEN(ROW()) breaks after a filter, because hidden rows leave two rows of the same colour together. This counts only visible rows:
=ISEVEN(SUBTOTAL(103, $A$2:$A2))Highlight the row of the selected cell?
Conditional formatting doesn’t know which cell is selected unless VBA tells it. In Excel 365 you can turn on View › Focus Cell instead.
Where people go wrong
| Symptom | Cause |
|---|---|
| Only column H changes colour | Formula written as =H2=… without $ on the column |
| Every row coloured, or the row above/below | $H$2 (row locked), or formula written for row 1 while the selection starts at row 2 |
| Rules multiply as you copy rows | Copy-paste duplicates rules; clean up in Manage Rules › This Worksheet, or use a Table |
| Rule misses “Overdue ” | Trailing space in data; use =TRIM($H2)="Overdue" |
Practice
Download the combo practice workbook below. Its Sales sheet of 200 orders (dates, regions, products, customers, amounts) is ready data to try every formula on this page, and the Practice sheet has 50 checked tasks on related combos.
More: all formula combos · Excel function course.
Stuck on a step? Ask a question and the AI answers using this article.