VBA Code to Email when certain cells fall below other cells value

[rmeagan]

Hi, I'm trying to get auto emails sent when certain cells fall below a certain value. For example Column G is current stock and column J is minimum stock. I want to send an email when G3 falls below J3 and same for when G4 falls below J4 and so on. I've tried the code below but is sending an email even when G3 is changed and doesn't fall below J3. Please help!

Dim xRg As Range
'Update by Extendoffice 2018/3/7
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Intersect(Range("G2:G500"), Target)
If xRg Is Nothing Then Exit Sub
If IsNumeric(Target.Value) And Target.Value < Range("J2:J500") Then
Call Mail_small_Text_Outlook
End If
End Sub

 

[Joe4]

Welcome to the Board!

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.CountLarge > 1 Then Exit Sub
    
    If Intersect(Range("G2:G500"), Target) Is Nothing Then Exit Sub

    If IsNumeric(Target.Value) And Target.Value < Target.Offset(0, 3).Value Then
        Call Mail_small_Text_Outlook
    End If

End Sub

 

[rmeagan]

Wow, I have no idea how or why this works but it does! You are a lifesaver! Thank you so much for the quick help.

 

[Joe4]

You are welcome. Glad I was able to help!

The real issue was with this line here, specifically the part in red:

If IsNumeric(Target.Value) And Target.Value < Range("J2:J500") Then

You have one particular cell in column G you are checking, and you are trying to compare its value to the whole range J2:J500, which really makes no sense (how can you compare 1 value to 499 at the same time, and expect to return a simple TRUE or FALSE?).

Really, you want to compare that particular value in column G with the value in column J of the same row.
So we do that with:

    If IsNumeric(Target.Value) And Target.Value < Target.Offset(0, 3).Value Then

"Target" is the cell you are checking (column G in some row).
So if we want to check the value in column J of the same row, we simply need to move from that Target cell over 3 columns to the right.
You can use OFFSET to do that (format is OFFSET(rows to move over, columns to move over)).

The rest I just did a little clean-up and removed some unnecessary steps and consolidated it down a little.

 

[rmeagan]

You rock! Thanks for taking the time to explain this. I'm still new and learning so this is so helpful. Thanks again!

 

[rmeagan]

Actually 1 more question! if the value in g is changed by a code or by a formula will this still work or will it only send if the value is changed manually?

 

[Joe4]

Actually 1 more question! if the value in g is changed by a code or by a formula will this still work or will it only send if the value is changed manually?

If it is changed manually or by code (soemthing that involves a direct update to that particular cell).

Changes that result due to formulas are not caught "Worksheet_Change" event procedures.
There is a "Worksheet_Calculate" event that fires when the sheet is re-calculated, but there is a BIG caveat to that.
It can only tell you that "some cell with a formula somewhere on the sheet was updated". It cannot target/tell you exactly which cell it was.
So it often isn't so helpful to use that event.

However, depending on what the formula is, there may be a way around that.
If the formula is ONLY referencing other columns in the SAME row, we can simply watch those other columns for manual/code changes.

For example, let's say the formula in cell G2 is:
=A2+B2
and columns A and B have values being entered in manually.
Then we can use a "Worksheet_Change" event procedure that fires whenever a value in columns A or B change, and then compare the values in columns G and J of that SAME row.

Hope that helps!