Dear Friends,

The below mentioned code does not run when a cell value change/update/enter by another macro in a range but working fine when i manually enter "Yes" in mentioned range and shoot the mail with corresponding data related to that row. I am stuck here and almost spend a week to find solution on this.

I am sure someone will help me here to solve this as i have limited knowledge of VBA.

Here is my code which shoot call another macro to send email.

Note : The value "Yes" entered by another macro in the range, after that this code detect and shoot the mail.


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("R1:R5001")) Is Nothing Then
        On Error GoTo Erreur
        Application.EnableEvents = False
        If LCase(Target.Value) = "yes" Then
            Dim answer As Variant
answer = MsgBox("Do you want to send final status mail to user?", vbYesNo + vbQuestion, "Final Status Mail")
            If answer = vbYes Then
Call Mail(Target.Offset(, -11), Target.Offset(, -13), Target.Offset(, -6), Target.Offset(, -2))
            Else
                'do nothing'
            End If
        End If
    End If
    GoTo Fìn
Erreur:
    Debug.Print Err
Fìn:
    Application.EnableEvents = True
End Sub