How can i fix the run-time error '91' in excel VBA?-CodePudding

Im trying to trigger a popup msg for column q if it differs from "Open" Or "Closed" which say Please enter Justification this code works well but it gives me the bellow error message if i click on a different column (other than Q)

how can i get rid of the message?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not (Application.Intersect(Range("Q2:Q3977"), Target) = "Open") And Not (Application.Intersect(Range("Q2:Q3977"), Target) = "Closed") Then
        MsgBox "Please Enter Justification"
    
    End If
End Sub

CodePudding user response：

One way is to just check the .Column of the Target to make sure it's Q first.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 17 Then
    If Not (Application.Intersect(Range("Q2:Q3977"), Target) = "Open") And Not (Application.Intersect(Range("Q2:Q3977"), Target) = "Closed") Then
        MsgBox "Please Enter Justification"
    
    End If
  End If
End Sub

There are probably much cooler ways.