Alert message

[tnagaway]

hello,
How can i have excel show an alert message when certain dates are 7 days away from the current date, example : i have an excel sheet with expiry dates for the merchandise in my store, i want excel to show a message box alerting me that a certain item is 7 days away from expiry ...... and the alert to be shown in another sheet, so i always know which items i should take off the shelves

 

[santeria]

Intriguing question... it does not seem to have been asked in this way... I wonder if there are some inventory control style templates, or if a change event style structure is what is needed??

Just a few thoughts.

 

[Smitty]

Welcome to the Board!

You can do what you want with formulas & a bit of conditional formatting:

Book1
	A	B	C	D
1	CurrentDate	Product	ExpirationDate	ProductStatus
2	12/26/03	Twinkies	12/26/03	Producthasexpired
3		Ho-Ho's	12/27/03	Productiswithin7daysofexpiration
4		Ding-Dong's	12/28/03	Productiswithin7daysofexpiration
5		7-Up	12/29/03	Productiswithin7daysofexpiration
6		RedBull	12/30/03	Productiswithin7daysofexpiration
7		BeefJerkey	12/31/03	Productiswithin7daysofexpiration
8		Doritos	01/01/04	Productiswithin7daysofexpiration
9		M&M's	01/02/04	Productiswithin7daysofexpiration
10		Gatorade	01/03/04	Productstatusfine
Sheet1

I applied conditional formats to the Product status. You could also write a Workbook_Open event to search for the dates in question, but depending on the amount of stock that you have on hand the number of message boxes that you'd have to contend with in your scenario could be quite cumbersome.

Hope that helps,

Smitty