Email alert if a cell changes due to conditional formatting

**GHI1968** · 12-21-2023, 11:27 AM

Hello everyone.

Is it possible for excel to send an email alert if a cell changes due to conditional formatting, please?

Dates in a column are for contract renewal dates.

Cells in this column are formatted to change colour at 100 days before their end date as an alert.

If possible I would like an email to alert me to the change, something simple to say 'Contract alert' etc.

I hope this makes sense?

Thank you.

**AliGW** · 12-21-2023, 12:04 PM

Not without VBA - shall I move the thread for you?

**GHI1968** · 12-21-2023, 12:08 PM

Yes, please Ali.

Thank you.

**mcsythera** · 12-22-2023, 10:13 AM

You would need to add code to the Worksheet portion of the code. Any time the workbook is opened, the following would check all dates, and any that match the criteria (<=100 days), it would display an email generated with the dates that match for whichever user opened the workbook. This doesn't touch the conditional formatting, or use it in any way, but does use the same criteria. If you'd prefer a method where the criteria is the formatting of the cells, you'd need to provide more information about which cells are changed and what they're changed to.

Please Login or Register  to view this content.

This will display an email that looks like this (assuming Column B is the name of the contract and Column C is the amount of the contract):

Contract in row 2 needs to be renewed.
Date: 3/22/2024 | Column B: Settleman Contract | Column C: $1,875,000

Contract in row 5 needs to be renewed.
Date: 3/26/2024 | Column B: Herman & Sons | Column C: $500,000

Etc, etc. You can change "Column B" in the code to be whatever, like "Contract Name", and "Column C" in the code to be "Contract Amount", or whatever you want to display in the email. Note that if you have a dollar amount, it'll display as plain text ($500,000 shows as 500000 in the email), but you get the idea. You can also change the criteria If DateDiff("d", Date, dateInColumnA) > 0 And DateDiff("d", Date, dateInColumnA) < 101 Then to be anything. Currently it just says if the current date is 1-100 days away from the contract renewal date. You can change that to 50-75, 25-90, -25-100 etc to meet your needs. Also note that the date column in this instance is Column A. Change dateInColumnA = ws.Cells(i, 1).Value to meet your needs (i, 1) is the row (i) and the column (1). If your contract renewal date is in Column D, change it to (i, 4).

Let me know if you need anything else.

**GHI1968** · 12-22-2023, 10:54 AM

Hi, mcsythera.

Thank you for your reply and help.

I just cant get it to work (I have never tried VBA before) I must be doing something wrong.

I want to pick up the dates in column H, at 100 days before contract end date receive an email reminder.

Any ideas?

Thank you.

**mcsythera** · 12-22-2023, 11:08 AM

I've attached a working sheet so you can see how it operates. Ensure you enable macros (the file is .xlsm and not .xlsx) and that you have the code in the right place. Since you've never used VBA before, I'd recommend testing out this sheet, showing the Developer tab, and looking through the code to familiarize yourself with what it does. You don't have to know exactly how it works, but the fundamentals of where it goes and how it does what it does can help you understand the backbone of the the sheet. You can copy the code from this test workbook to your actual workbook, save as a .xlsm file, and give that a go as well to see it working with your real dataset.