danielpt78
New Member
- Joined
- Mar 5, 2022
- Messages
- 9
- Office Version
- 365
- Platform
- Windows
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!
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!
VBA Code:
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