warning when a due date is a week away

May 26, 2011 at 00:17:11
Specs: Windows XP
I want to change a cell colour if the date in the cell is 7 days before today orange, if greaterthn today red

eg today is 08/02/11 and relevant cell date is 10/01/11 white
eg today is 08/02/11 and relevant cell date is between 01/02/11 and 07/02/11 Orange
eg today is 08/02/11 and relevant cell date is greater than today red

using office 2007


See More: warning when a due date is a week away

Report •


#1
May 26, 2011 at 06:00:15
Read the Excel help files on Conditional Formatting or Google:

Excel 2007 Conditional Formatting

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
Report •

#3
June 29, 2011 at 02:51:23
None of these meet my requirements

Cell H3, H4, H5 etc have a date which is a predicted date, I need each cell to change colour if the date is a) Red - 7 days prior to today, b) Orange if it is 14 days, once action taken and a new date entered cell to have no colour


Report •

Related Solutions

#4
June 29, 2011 at 08:35:48
Why do you say that the threads suggested by Mike don't meet your requirements?

From everything you've told us, Conditional Formatting with the following 2 rules sounds like the way to go.

I just tried this in Excel 2003 and it works fine for me. I don't have 2007 on this machine, but there's no reason that the same rules shouldn't work for in 2007.

=H3>=TODAY()-7

Format...Patterns...Red

=H3>=TODAY()-14

Format...Patterns...Orange

With "today" being 6/29/2011...

6/21/2011 turned H3 Orange
6/22/2011 turned H3 Red

Isn't that what you want?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
July 1, 2011 at 02:08:57
The reason because I totally screwed up. my big mistake was if the date in H3 is within that criteria it works but I forgot to factor in that when action has been taken and therfore the date changes i.e greater than the 14 or 7 days it still shows RED

Today being 01/07/11
Cell H3 had 23/06/11 and yes it went Orange, I then changed the date to today 01/07/11 and the cell turned Red, however when I changed the date (having taken action and new date was now 30/07/11) the cell still remained Red,

So apologies to Mike as without this added factor his formating worked.


Report •


Ask Question