Select the full data range and create a formula rule that locks the status column while allowing the row to change.
Visual walkthrough
Home›Conditional Formatting›New Rule
Before
A2:D20 is the target range. D4 contains Overdue.
After
The full row A4:D4 changes color.
Check the result
Change D4 to Completed. The highlight should disappear.
Step-by-step instructions
- Select A2:D20, with row 2 as the first data row.
- Choose Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format. Enter the formula below.
=$D2="Overdue" - Choose a fill color, then confirm. In Manage Rules, check that Applies to is =$A$2:$D$20.
- Set D4 to Overdue and check that all cells in A4:D4 are highlighted. Change it to Completed and confirm that the highlight disappears.
Frequently asked questions
Why is the wrong row highlighted?
Check the first row of Applies to and the row reference in the formula. For a range starting at row 2, the formula should also use row 2.
Reference: Microsoft Support