Conditional Formatting - Changing A Separate Cell's Colour If Another Cell Is Out Of Date

[_brian_]

Hello Everyone,

I've been trying hard to get this done myself, and I've tried following some online tutorials but I just don't seem to be getting anywhere.

I am wanting to change a cell colour - which contains non-related data - depending on another cell's date in relation to the current date.

For example.

I would like to change the colour of cell B2 to green if the date contained within cell B3 is greater or equal to 1 year from the current date, yellow if cell B3 is less than 1 year from the current date, orange if cell B3 is less than six months from the current date, and red if the date in B3 is older than the current date.

I hope that's clear enough!

Green >or= 1 year from current date
Yellow < 1 year but > or = 6 months
Orange < 6 months but > or = current date
Red < current date

Thanks to anyone who can give me a hand.

[Aardigspook]

Welcome to the forum.

Select the range you want to format (B2:AB2 for example).
Click 'Conditional Formatting' then 'New Rule' then 'Use a formula to determine which cells to format'.
Enter this rule into the formula box (labelled 'Format values where this formula is true') and choose Green fill using the 'Format' button:

Please Login or Register  to view this content.

Repeat using these formulae:

Please Login or Register  to view this content.

↑ with Yellow fill,

Please Login or Register  to view this content.

↑ with Orange fill,

Please Login or Register  to view this content.

↑ with Red fill.

That should do what you want.


Edit:
You could use simpler formulae if you were happy with 182 and 365 days in place of exact year / 6 months. For example, the yellow fill would then be:

Please Login or Register  to view this content.

[_brian_]

Aardigspook, you're a life saver, works perfectly - thankyou very much!

[Aardigspook]

You're welcome, glad to be of help and thanks for the rep.

If that takes care of your original question, please take a moment to mark the thread as Solved so others know there's an answer here (see my sig for instructions). Thanks.