Alerts

[sirofsirs]

I am trying to create an excel document that will contain calibration dates of certain items. When the item has 7 days left until the it's out of date the item in the list will change color and an email will be sent out to a given list of email addresses. Is there a way to do this?

Matt

 

[Zack Barresse]

Hi sirofsirs, aka Matt, welcome to the board!

You can change the color (format) of a cell with Conditional Formatting, found in the Format menu. The Help files should be sufficient on this, but if not do a board seach for it and you will have more than enough info to get the job done.

As far as sending out an email, that is asking quite a bit more and you'll need VBA for that. It is a very large jump from coloring a cell to sending out an email. Could perhaps something else trigger the event to send out an email? Perhaps a user's keystroke or clicking of a button perhaps? This is generally a *safer* way to go. Of course, anything can be done. If this is what you would like to pursue, please provide all details necessary, such as email client, to, cc, bcc, subject, body, etc.

 

[sirofsirs]

Well I got the color portion of it to work for the most part. Although how do I get the color alert to be extended throughout the excel worksheet so that when things are added later they will also be included in the condition?

About the email, the reason for the email would be so someone wouldn't need to check on the excel file every day, only when alerted. As for more details, I don't really want to put a list of email addresses for my company out here on the net and was hoping it was something that I could add later. The body would consist of the item, model, s/n, and date needed to be calibrated. All of these things would be entered in entered into different cells (one row) of the excel document. In which case they could all be referenced to be put into the body of the email.

As a side note, I do have some experience with C++ and html, so with a little time I'm sure I would probably be able to decypher most of the code done in VBA.

 

[Zack Barresse]

Ok, good. How about this for an email notif:
A boolean variable is set publically when the workbook is opened. When any number of conditions is met this variable is tripped. If this happens, it causes an email to be sent out with all the specifications you lay forth. If these circumstances happen again the email would not trip because the boolean value would have also been tripped, thus negating the email process. Make sense?

As far as extending the CF for the rest of the worksheet, you can add CF's to anything you want, and as far as you want. Let me give you an example. Say you have dates in column D with a one-row header in row 1. You want the entire row highlighted if the date in column D is greater than today's date, but you do NOT want anything highlighted where there is no data in column A.

This is what you would do. Where the column headers (A, B, C ..) meet the row headers (1, 2, 3 ..) there is a single square (generally grey) with nothing in it, click that, it should select all of the cells. Once you've clicked that, click Format | Conditional Formatting | Formula Is. NOTE: This is assuming that no other conditional formats exist anywhere on the worksheet!

=($A1<>"")*($D1>TODAY())

Think of the * sign as an AND function. This says if the current (active) row in A is not blank AND the current (active) row in D is greater than todays date, then the condition is true and color me. The entire row should meet that condition as the columns were made absolute with the $ sign.

HTH

 

[sirofsirs]

Thanks very much, I seem to have gotten it all working pretty well with most of the bugs ironed out! You've been much help. Thanks again.

Matt