Automated Email at Specific Value Reached Doing My Head In!

Hi There,

I am somewhat frustrated with VBA code that does what it is meant to do, but with a caveat.

What I want to happen is an email to automatically be sent when a specific value is reached within the range M3:M20. The specific value is 4 (see truncated screenshot).

Untitled.jpg

I am using the following code:

Please Login or Register  to view this content.

Now the thing is, any movement on the worksheet generates an email to display, so I need to stop it from doing that, and I need the macro to display the email once for each time the number 4 appears in the range M3:M14. At the moment it generates multiple emails for each occasion 4 has been reached within the range. I need it to generate the email once only for each 4 reached and not repeat it when another 4 is reached further down the range. I think I need a line of code so that it does not treat past occasions for when four was reached as if they were new instances.

If M3 is 4, then it would generate an email, if M4 does not reach 4, no email is sent, but if M5 is 4 then an email is sent, but not an email for M3 (even though the value remains 4). I want singular emails to be sent once only for each occasion the value 4 is reached, but earlier occasions are ignored.

Can anyone help?

Best regards

Please edit your post and add code tags.

-
AA Add Code Tags.png