Results 1 to 2 of 2

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.


    Code:
    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.


    Code:
    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!

  2. #2
    Acolyte Sixthsense's Avatar
    Join Date
    Nov 2012
    Location
    India
    Posts
    26
    Articles
    0
    Try this....

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •