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!

Hi, 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.

Regards

Wow, Thank you very, very much. This is exactly what i was looking for. Very helpful, Cheers!

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","")

Regards

Ask Your Question

Weekly Poll