excelvba

Running two separate macros from 2 columns on the same sheet


I would like to run 2 separate emails from 2 columns in the worksheet.

I have one email pattern for the column P and the other one for column Q

Unfortunately the code below doesn't collaborate with my idea

       Private Sub Worksheet_SelectionChange(ByVal Target As Range)

       If Not Intersect(Target, Range("P:Q")) Is Nothing Then
           If Intersect(ActiveCell, Range("P")) Is Nothing Then
               Call EmailRequest
           Else
               Call EmailApproval
           End If
       End If
       End Sub

I am getting the following error:

Run-time error 1004
Method 'Range of objects' Worksheet failed.

How could I run 2 separate macros from 2 separate columns?


Solution

  • Pls try.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Not Intersect(Target, Me.Columns("Q")) Is Nothing Then
            Call EmailRequest
        ElseIf Not Intersect(Target, Me.Columns("P")) Is Nothing Then
            Call EmailApproval
        End If
    End Sub