VBA Error code 91

[Notrom]

I am a novice VBA user and I just can't get this code to work without error codes. Can some one see why it's not working?

When a drop down list in cells in column B show either of the 2 target phrases, the MsgBox should appear, which it does. However, when I click into other cells, the error code appears!

 

[Joe4]

Welcome to the Board!

That is because Intersect returns a range. If the update is outside of column I, it will return the empty range. You cannot check the value of an empty range.

Try something like this instead:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
    Set rng = Intersect(Target, Range("I5:I1500"))
    
'   Exit if cell updated not in watched range
    If rng Is Nothing Then Exit Sub
    
'   Loop through updated cells in watched range
    For Each cell In rng
        If cell.Value = "Change in Working Pattern" Or cell.Value = "New Staff Member" Then
            MsgBox "Please input the hours..."
        End If
    Next cell
    
End Sub

Also, in the future, please post your actual VBA code instead of just an image of it, so we can copy it over and edit it (you cannot do that with an image!).
See here for details on how to do that: How to Post Your VBA Code

 

[Notrom]

Welcome to the Board!

That is because Intersect returns a range. If the update is outside of column I, it will return the empty range. You cannot check the value of an empty range.

Try something like this instead:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
  
    Set rng = Intersect(Target, Range("I5:I1500"))
  
'   Exit if cell updated not in watched range
    If rng Is Nothing Then Exit Sub
  
'   Loop through updated cells in watched range
    For Each cell In rng
        If cell.Value = "Change in Working Pattern" Or cell.Value = "New Staff Member" Then
            MsgBox "Please input the hours..."
        End If
    Next cell
  
End Sub

Also, in the future, please post your actual VBA code instead of just an image of it, so we can copy it over and edit it (you cannot do that with an image!).
See here for details on how to do that: How to Post Your VBA Code

Welcome to the Board!

That is because Intersect returns a range. If the update is outside of column I, it will return the empty range. You cannot check the value of an empty range.

Try something like this instead:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
   
    Set rng = Intersect(Target, Range("I5:I1500"))
   
'   Exit if cell updated not in watched range
    If rng Is Nothing Then Exit Sub
   
'   Loop through updated cells in watched range
    For Each cell In rng
        If cell.Value = "Change in Working Pattern" Or cell.Value = "New Staff Member" Then
            MsgBox "Please input the hours..."
        End If
    Next cell
   
End Sub

Also, in the future, please post your actual VBA code instead of just an image of it, so we can copy it over and edit it (you cannot do that with an image!).
See here for details on how to do that: How to Post Your VBA Code

Thank you so much for the speedy reply.

Sorry I posted incorrectly, I was rushing! I will check all the FAQ and other info before I post again.

I will try this code as soon as I can, I do appreciate your reply

 

[Joe4]

You are welcome!