Forum Discussion
Use Conditional Formatting to Highlight Cell Past the Date
Hello! I have a spreadsheet with task assignments for my team. It is a table.
Column A is the name of the person assigned, header is Marketing Advisor.
Column B is the date they were assigned, column title is Assigned Date.
Column C is a formula of =[@[Assigned Date]]+7. Column title is Due Date.
Column D is the date it was completed, titled Completed Date
I would like to use conditional formatting to highlight the Due Date in red if it is past the due date and no completed date is entered. Is this something I can do? If so, can you please provide the formula?
I've redacted the names for privacy, but here is the spreadsheet. Thank you!
Select from C2 to the last cell in the table in column C. C2 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=AND($C2<TODAY(), $D2="")
Click Format...
Activate the Fill tab.
Select red as fill color.
Click OK, then click OK again.
Select from C2 to the last cell in the table in column C. C2 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=AND($C2<TODAY(), $D2="")
Click Format...
Activate the Fill tab.
Select red as fill color.
Click OK, then click OK again.- Thank you so much! That did the trick!
