Donate SIGN UP

Excel Conditional Formatting

Avatar Image
Dizzieblonde | 17:48 Thu 31st Oct 2013 | Technology
7 Answers
Bit of a complicated one, but probably not to a whizz on Excel! I'm basically trying to format a spreadsheet of dates to change colour in each cell when the due date is approaching (e.g. Amber 1 week before, Red 1 day before etc.) but am stumped.

I've managed to do the overdue ones by putting the =TODAY()-B1 (or whichever cells)>7 etc. to flag up when it's overdue by a week, but can't seem to find out how to set it to warn me when it's coming, such as one month before, 2 weeks before, 1 week before

Any PC masters with any ideas? :(
Gravatar

Answers

1 to 7 of 7rss feed

Best Answer

No best answer has yet been selected by Dizzieblonde. Once a best answer has been selected, it will be shown here.

For more on marking an answer as the "Best Answer", please visit our FAQ.
Use the same formula as you already have, but just add multiple entries to the conditional formatting for that cell. However, you need to have them in the correct order, i.e. from lowest limit to highest. For example, condition 1 should be
The end of my answer disappeared! It was:

"... condition 1 should be >7, condition 2 should be
Question Author
Thanks Aquariel - What would be the code for it though? Would the arrow (< or >) need to be a particular way? I don't want it to countdown until 'today', but if - say - it'll recognise I need to tax the car on 12th December 2013, it'll warn me one month before in yellow, two weeks before in amber, then a week before in red
AB seems to be a bit dodgy with the backets in my reply (hence the missing end of my answers). I have uploaded a pic of the conditional formatting I use to achieve this, hopefully this will work!



So between 14 and 7 days beforehand, it shows blue, between 6 and 0 days before it shows green, and red for anything over. You can easily adjust the formulas for whatever limits you require. There's a minor error in the second formula (-7 instead of -6), but you should get the idea.
Aaaargh! Things just aren't working right for me today! Here's the pic.

http://oi39.tinypic.com/2a0e0as.jpg

Question Author
Brill - the pic makes it so much clearer. Would I have to do each cell in turn (there's thousands!), or could I do a column at a time - e.g. 10 people's car tax
The formula isn't absolute, so you can copy and paste it to any other required cells - use Edit/Paste Special and select Formats. That will copy the formatting and won't delete your cell contents. If you change the formulas, you will need to copy them again.

You could enter the limit values somewhere in your spreadsheet, and change the conditional formulas to refer to these values, then you can change them without having to copy the formulas every time. I would have done it this way if I was creating a new spreadsheet.

1 to 7 of 7rss feed

Do you know the answer?

Excel Conditional Formatting

Answer Question >>