Conditional Formatting

January 12, 2010 at 09:05:33
Specs: Windows XP
I recently programmed a pop up in excel to notify the users for my company if a list of dates is about to expire prior 30 days notice. The problem is that several sections of the company do not allow macros and this cannot be fixed.

I am wondering if there is a conditional formating option or formula where a single cell could turn red if the following list of Dates are 30 days less than today. My problem is turning one cell red if ANY of the cells, say from A1-A20, have dates 30 days < today.

Any help is appreciated. Thank you!

See More: Conditional Formatting

Report •

January 12, 2010 at 09:43:25

One way to do this is have a second column, B1 to B20 which responds to the date difference, e.g., in B1 =IF(A1-NOW()<30,"X","")
cells B1 to B20 will show an 'X' if the date criteria is met, (adjust the logic as required).

In the cell that shows the warning, use conditional formating.
Select: Formula Is in the Condition 1 drop-down
Enter this formula =COUNTIF($B$1:$B$20,"X")
Select a format for the cell.

The conditional formating formula counts the number of X's in column B.
If there are no X's in column B the result is zero which is evaluated as False and there is no conditional formatting.
If the formula returns a number greater than zero, it is evaluated as TRUE and the conditional formating is applied.

The 'column B' test does not have to be in column B - it could be in a hidden column or out of sight say in column AB.


Report •

January 12, 2010 at 10:07:40
Wow, Thank you very, very much. This is exactly what i was looking for. Very helpful, Cheers!

Report •

January 12, 2010 at 10:57:25
You're welcome.

You could also format the message in the 'warning' cell in white, so that it doesn't show and then use the conditional format to turn the text black when the date criteria is met.

Or use a formula in the 'warning' cell such as =IF(COUNTIF($B$1:$B$20,"X")>0, "Message","")


Report •

Related Solutions

Ask Question