Macro not trigger on cell value change

Replace your current (PartsData) worksheet Change Event with this one

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim answer As Variant

If Not Intersect(Target, Range("R1:R5001")) Is Nothing Then
    If LCase(Target.Cells(16).Value) = "yes" Then
        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))
        End If
    End If
End If

End Sub