Automatically changing colours in

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:16 PM GMT


QUESTION: I have anspread sheet with dates in it. I want to know if it possible to haveautomatically change the background colour when the dates get to a certain point. So for example if something is 3 months away the background would be green, if it is a month away the colour would be green. It the date has passed then it would be red. I really hope you can help and

ANSWER: You didn't specify version, but unless you are on a REALLY old one then you need to simply look at format, conditional formatting -this allows you to set up rules - in your situation the way to go would be with formulas, something like


where A1 is the cell with the date - this test produces a logical true/false answer and can be used to set the format of the cells. You can have up to 4 levels of colour (three in the conditions, one as the base format of the cell).

---------- FOLLOW-UP ----------

QUESTION: Hi Aidan Many I am using2003. Is the conditional formatting still able to help with this problem and what query would I use?

Many === Answer ===

that will be no problem at all - my example formula would be the one to use for a date of 3 months ago (I'm GUESSING that it doesn't matter that it's not exact) - take a look at the command and HOPEFULLY it will become clear, but if it helps email me and I can send you sample files - my email is


©2021 eLuminary LLC. All rights reserved.