Sending multiple emails from one change event

[DYB]

I am a novice at VBA but have a change event in a spreadsheet to send three different emails based on dates in three separate ranges. The code does work and will send the emails but it sends the incorrect emails for each range so it is clearly incorrectly constructed.

Range 1 sends the emails for range 2 and range 3
Range 2 sends the emails for range 1, range 2 and range 3
Range 3 sends the emails for range 1 and range 2

The code I am using is below:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
On Error Resume Next

If (Target.Count > 1) Then Exit Sub
Set Rng1 = Intersect(Target, Range("S3:S503"))
Set Rng2 = Intersect(Target, Range("AD3:AD503"))
Set Rng3 = Intersect(Target, Range("AN3:AN503"))

If Rng1 Is Nothing Then
End If
If Rng2 Is Nothing Then
End If
If Rng3 Is Nothing Then
End If

If Rng1(Target.Value = Date) Then
Call Mail_USNew_Outlook(Target)
End If

If Rng2(Target.Value = Date) Then
Call Mail_INNew_Outlook(Target)
End If

If Rng3(Target.Value = Date) Then
Call Mail_UKNew_Outlook(Target)
End If

End Sub


Any help reconstructing the code would be gratefully received.

 

[Logit]

The following works here. You will need to edit the ranges :

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim aAddr, bAddr, cAddr As String
Dim rng1, rng2, rng3 As Range, cel As Range

    aAddr = "A1:A5"  ' << change
    bAddr = "B1:B5"
    cAddr = "C1:C5"
    
    Set rng1 = Intersect(Range(aAddr), Target)
    Set rng2 = Intersect(Range(bAddr), Target)
    Set rng3 = Intersect(Range(cAddr), Target)


    If Not rng1 Is Nothing Then
        For Each cel In rng1
            If IsDate(cel) Then
                MsgBox "Range A"
                Exit Sub
            End If
        Next
    End If
    
    If Not rng2 Is Nothing Then
        For Each cel In rng2
            If IsDate(cel) Then
                MsgBox "Range B"
                Exit Sub
            End If
        Next
    End If
    
    If Not rng3 Is Nothing Then
        For Each cel In rng3
            If IsDate(cel) Then
                MsgBox "Range C"
                Exit Sub
            End If
        Next
    End If
    
End Sub

 

[DYB]

Thank you so much, that works fine!

Many thanks.

 

[Logit]

You are welcome.