Help with running a macro when certain cells change value and meet a criteria

[Random1432]

The project i'm working on is a spreadsheet that I use to monitor stock levels of a product. In total there are 74 different products on the spreadsheet.

I've got the macro working perfectly so that it will send a email to the specified location etc.

The part i'm stuck on is getting the vba code to know when to send the email.

What I want to happen is that whenever the volume of stock on any one of the products (L4:L78) drops bellow 500, it will run my macro email to inform the client their stock is low.

I've managed to make this work with just one cell, but it wouldn't let me adapt it to affect all the cells.

The cells, L4 for example is a calculated total of new stock inputs and outputs B4:K4.

Here are some codes I have tried that couldn't make fully work.

Please Login or Register  to view this content.

This one worked but I couldn't make it work for more than one cell. eg. chaning [L4] to [L4:L78] made that code unusable.

Please Login or Register  to view this content.

Again had errors making this work.

Any help of advice would be hugely appreciated as this is starting to be far to time consuming for someone with limited vba knowledge where I think it would be a very easy fix for the pros!

[Bernie Deitrick]

First, you need some way to know when you have already sent the email, so let's say that column M of the same row is available. Further, you need to re-write you emailing macro to know which cell you have just analyzed. And even further, you need to decide when to re-contact - this will email every time the value in column L drops below 500 or further drops.

Please Login or Register  to view this content.

This will only contact once when the value drops below 500

Please Login or Register  to view this content.

This is what you need to do to start to change your mailing macro:

Please Login or Register  to view this content.

You also need to decide what to do when the value rises above 500 from below - both examples re-set the contact flag.