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

[Random 1432]

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.

Private Sub Worksheet_Calculate()
If [L4] < 500 Then
Call Mail_small_Text_Outlook


End If
End Sub

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.

private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Set KeyCells = Range("L4", "L5")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
        Is Nothing Then
           If IsNumeric(KeyCells) And KeyCells < 500 Then
            Call Mail_small_Text_Outlook
    End If
End Sub

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!

 

[Sixthsense]

Try this....

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Calculate()
    x = Evaluate("=SUMPRODUCT(ISNUMBER(L4:L78+0)*(L4:L78<>"""")*(L4:L78<500))")
    [color=darkblue]If[/color] x [color=darkblue]Then[/color] [color=darkblue]Call[/color] Mail_small_Text_Outlook
[color=darkblue]End[/color] [color=darkblue]Sub[/color]