Formulate cells to change colour

[Greg_Nott]

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.

[#2 · Accepted solution]

@Greg_Nott 

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.

[#3]

@Hans Vogelaar 

HI Hans

I worked a treat, thanks so much :)

Cheers Karen