Cell.Target Value not working when the value comes from a formula

[danielpt78]

Hi all, hope you are well today, i have a little problem in my macro.
This macro is getting the value from a cell in F collumn and comparing it to the cell in G column. F is stock column (Which is chaning with a formula) and G is the minimum stock (Which is always a fixed value).

The macro only works when I input the value in F Column manually without using a formula (in this case is Column D (Inbound) - Column E (Outbound ))

Hope you understood my explanation and can help me! Thanks a lot!

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("F2:F5000")) Is Nothing Then
    If Cells(Target.Row, 6).Value = Cells(Target.Row, 7).Value Then
        Application.EnableEvents = False
        Cells(Target.Row, 10).Value = Cells(Target.Row, 10).Value + 1
            Dim xOutApp As Object
            Dim xOutMail As Object
            Dim xMailBody As String
            Set xOutApp = CreateObject("Outlook.Application")
            Set xOutMail = xOutApp.CreateItem(0)
            xMailBody = "Mail Text"
            On Error Resume Next
            With xOutMail
            .To = "Email Address"
            .CC = ""
            .BCC = ""
            .Subject = ""
            .Body = xMailBody
            .Display 'or use .Send
            End With
            On Error GoTo 0
            Set xOutMail = Nothing
            Set xOutApp = Nothing
        Application.EnableEvents = True
    ElseIf Cells(Target.Row, 6).Value < Cells(Target.Row, 7).Value Then
        Application.EnableEvents = False
        Cells(Target.Row, 11).Value = Cells(Target.Row, 11).Value + 1
        Application.EnableEvents = True
        End If
End If
End Sub

 

[danielpt78]

I think i explained wrong, the cell that matters the most for the stock is the column C in both sheets, A column is the input of the ID of the material and the value that goes to the sumif formula above is the one inputed in the C column

 

[DanteAmor]

The Worksheet_Change event fires when you modify a cell. If the cell is the result of a formula, you are not really modifying the value of the cell, what is changing is the result of the formula, but the formula remains the same.

As I mentioned at the beginning. The cells you modify are the ones that trigger the event. If none of the cells are modified as they come from the result of other sheets then you need to use another approach.

 

[danielpt78]

As I mentioned at the beginning. The cells you modify are the ones that trigger the event. If none of the cells are modified as they come from the result of other sheets then you need to use another approach.

Ok thanks a lot for the help! I will try to figure out another way to get to what i want. Have a nice day!

 

[DanteAmor]

If the cells that interfere with the result of cell F2 are in the Inbound Sheet table, then the event should go in the Inbound Sheet table, that is, you should check when a cell on that sheet is modified and check the result in F2 to send the mail.