You have a status column — “Approved”, “Pending”, “Rejected” — and you want the entire row to change color based on that status. Not just the status cell, the whole row. This makes scanning a large spreadsheet instant — green rows are good, yellow rows need attention, red rows need action. Conditional formatting handles this with one setup that applies automatically to new data.
The Data
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order # | Customer | Amount | Status |
| 2 | 1042 | Acme Corp | $4,200 | Approved |
| 3 | 1043 | Bright LLC | $1,850 | Pending |
| 4 | 1044 | Chen Inc | $6,400 | Rejected |
| 5 | 1045 | Delta Group | $3,100 | Approved |
Step-by-Step Setup
1Select the entire data range (A2:D100 or however far your data extends). Include extra rows for future data.
2Go to Home → Conditional Formatting → New Rule
3Select “Use a formula to determine which cells to format”
4For the Approved (green) rule, enter:
=$D2="Approved"
The $D locks the column to D (Status) so the formula checks column D for every cell in the row. The 2 without a dollar sign means it adjusts for each row. Set format to green fill.
5Repeat for Pending (yellow) and Rejected (red):
Pending — yellow fill
=$D2="Pending"
Rejected — red fill
=$D2="Rejected"
The dollar sign is the key
$D2 means “always look at column D, but adjust the row.” This is what makes the entire row color based on one cell. Without the $ on D, each cell in the row would check a different column — which isn’t what you want.
Color-Coding by Numeric Ranges
For coloring rows based on a number (e.g., order amount thresholds):
High value (green) — above $5,000
=$C2>=5000
Medium value (yellow) — $1,000 to $4,999
=AND($C2>=1000, $C2<5000)
Low value (red) — below $1,000
=$C2<1000
Alternating Row Colors (Zebra Striping)
For readability without status-based logic:
=MOD(ROW(),2)=0
Colors every even-numbered row. Set to a light gray or light blue fill. This creates the zebra stripe effect common in professional tables.
Multiple Conditions Combined
Color a row only when it's BOTH overdue AND high-value:
=AND($D2=5000)
Red fill only when the date in column D is past AND the amount in column C is $5,000+.
Performance with many rules
Each conditional formatting rule is evaluated for every cell in the range on every change. With thousands of rows and multiple rules, this can slow the workbook. For very large datasets, consider using VBA to apply static colors on demand instead of live conditional formatting.
When Formulas Aren't Enough
Conditional formatting handles simple color rules well. But dynamic business logic often requires more:
- Complex multi-column rules — color based on 5+ conditions across different columns and sheets
- Dynamic thresholds — color breakpoints that change based on a settings table, not hardcoded in rules
- Icon sets and data bars — embedding visual indicators beyond simple fill colors
- Performance on large data — applying colors to 50,000+ rows without lag
- Formatted reports — combining row coloring with borders, merged cells, headers, and print layout
A custom VBA formatting engine applies complex color rules instantly across any data size, with dynamic thresholds, icon embedding, and full report formatting.
"David is a fantastic professional to work with. He understood our requirements and was very patient and helpful throughout."
Cameron H. · Dartmouth · PeoplePerHour
Need Color-Coded Reports That Build Themselves?
Tell us about your reporting needs and we'll automate the formatting.
Start a Project