Forum Discussion
Formulate cells to change colour
Hi I am creating a spreadsheet with client plan expiry dates to flag a colour when they reach a certain date.
For example - Cell E4 = client's expiry date, I want cell F4 to change to yellow, 3 mths before to expiry date based on computer system date (as a reminder) and cell G4 to flag red when date reaches 1 mth before expiry date.
This can be done using conditional formatting.
Select F4 (plus cells below if want to format multiple rows).
On the Home tab of the ribbon, select Conditional Formatting >New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=AND($E4<>"",$E4<=EDATE(TODAY(),3))
Click Format...
Activate the Fill tab.
Select yellow.
Click OK, then click OK again.
Repeat the above steps with the formula
=AND($E4<>"",$E4<=EDATE(TODAY(),1))
and red.
2 Replies
This can be done using conditional formatting.
Select F4 (plus cells below if want to format multiple rows).
On the Home tab of the ribbon, select Conditional Formatting >New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=AND($E4<>"",$E4<=EDATE(TODAY(),3))
Click Format...
Activate the Fill tab.
Select yellow.
Click OK, then click OK again.
Repeat the above steps with the formula
=AND($E4<>"",$E4<=EDATE(TODAY(),1))
and red.
