Excel Conditional Formatting

[Dizzieblonde]

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? :(

[Aquariel]

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

[Aquariel]

The end of my answer disappeared! It was:

"... condition 1 should be >7, condition 2 should be

[Dizzieblonde]

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

[Aquariel]

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.

[Aquariel]

Aaaargh! Things just aren't working right for me today! Here's the pic.

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

[Dizzieblonde]

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

[Aquariel]

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.