Setting up an email alert when conditional formatting changes a cell

Welcome to the Forum CLRG!

Does C1 have this formula:

Formula:

Please Login or Register  to view this content.

?

There is no easy way to detect when conditional formatting changes, and at the root of it, what you really want is to determine when a date threshold is crossed.

There is no formula that can do what you want. This requires VBA. We need two things:

1. A method for triggering the VBA. You can do this manually, or you can have something to automatically check once a day. If you check once a day, you will need a cell someplace to record the last time it was checked so it knows if it should check again.

2. A record of when an email was sent for each time threshold, for each license. This is to know an email has been sent so that duplicates are not sent.

You will also need to be specific about what recipient, subject, and body you want in the email.

To make this go faster, please attach a file with sample data, but not private data. See yellow banner at the top of the page.

Originally Posted by CLRG

I've uploaded file

I don't see the file link. Sometimes this happens if the file is too big and the error message is not prominent. If your file is bigger than 1MB you will need to zip it; a zip file attachment can be up to 9.77MB. Please try again using one of these methods:

To attach a file, use the Paper Clip icon and follow instructions, or use this method:

1. Under the text box where you type your reply click the Go Advanced button.
2. On the next screen scroll down and under the Attachments section click the Manage Attachments link, which will show a pop-up window.
3. Click the Choose File button to select a file to attached.
4. Click the Upload button to upload the file.
5. Then click the Close This Window button. Your file is now attached to your post.

I have the file now.

For your CF rules I suggest using TODAY(). The result is an integer that will be an exact match for today's date. But if you use NOW() it is greater than TODAY() and won't match today's date. If you care about days and not times, it is cleaner to use TODAY(). I recommend putting 60 in a cell and refer to that instead of hard-coding 60 into the rules. If you ever wanted to change it, that would make it easier.

This sample file has just one set of dates, so I am providing a solution that will work for that.

This version will check once a day for changes in status. It will check when the file is opened and each time it is recalculated, but it will only actually check the statuses the first time each day this happens.

I have done only cursory testing so you should test this to make sure it does what you want. To test:

Clear the date in B1 on the Parameters sheet
Clear the Email dates in C6:D7 on the data sheet
Select any date and either change it, or hit F2 then ENTER. This will force VBA to recognize a change.

The code will run automatically as soon as you open the file, and again any time there is a change to the worksheet data.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.