Need Help with code to email - Giving me a Headache.

[peez]

What I am trying to do is when a worksheet opens if the value in column L (Due Dates) is greater than M6(Today's date) and if column N(not emailed already is not yes) then send an email. The problem is the only way I have been able to make it work is by using SelectionChange. The sheet auto calculates all information so changes aren't really made just calculated. I have conditional formatting that turns cell red if they are overdue. What I want to do is if any cell in column L is past M6 to send an email unless column N indicates YES. Here is the code I have so far:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

    
    If (Me.Range("A" & Target.Row).Value <= Me.Range("e1").Value) Then
        If (StrComp(Me.Range("H" & Target.Row).Value, "Yes", vbTextCompare) <> yes) Then
        Send_Email
        Else
        Exit Sub
        End If
    Else
    Exit Sub
    End If
    

Application.EnableEvents = True
End Sub

Any and All help is appreciated. Thanks in Advance. This problem has been racking my brain

 

[Smitty]

Welcome to the Board!

To capture changes that are caused by Calculation, you can use Worksheet_Calculate instead of Change.

Hope that helps,

Smitty

 

[peez]

Thanks for the reply but,

When I used Calculate it didn't work. I have tried this code on another worksheet that physically takes changes and it works but when I try calculate in my sheet it does nothing.

 

[Smitty]

Can you post what you came up with Calculate?

Note that the Calculate event doesn't use the Target argument, so you have to test differently.

Smitty

 

[peez]

Just to let you know I am a beginner in VBA but here is what I did:

Private Sub Worksheet_Calculate()
Application.EnableEvents = False

    
    If (Me.Range("A" & Target.Row).Value <= Me.Range("e1").Value) Then
        If (StrComp(Me.Range("H" & Target.Row).Value, "Yes", vbTextCompare) <> yes) Then
        Send_Email
        Else
        Exit Sub
        End If
    Else
    Exit Sub
    End If
    

Application.EnableEvents = True
End Sub