Worksheet_Change and Inersect code hangs if row is deleted

[Atholl]

Hello all,

The code below performs a function if any cell in the range G5:G1000 is altered, and it works very well. However, sometimes I need to delete rows which intersect the range. When I do this, Excel hangs and the only way to exit this is to click to 'X' in the outermost top right of the screen to exit Excel, then when it asks if I want to save changes, I click cancel and things are back to normal. Can anyone offer any suggestions to avoid the hang up when a row is deleted?

Atholl

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
    On Error GoTo err_fix
   

If Not Intersect(Target, Range("G5:G1000")) Is Nothing Then
If Target.Value = "ATM" Then MsgBox "Please ensure that Task is allocated", vbInformation, "ATR"
    
End If

Application.ScreenUpdating = True
    Exit Sub
   
err_fix:
    If Err.Number = 13 Then
        Exit Sub
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
    
Application.ScreenUpdating = True

End Sub

 

[Richard Schollar]

Hi Atholl

Not certain that this will work for you (I haven't replicated the error you are seeing) but try inserting:

If Target.Count>1 Then Exit Sub

Below the Sub name.

Best regards

Richard

 

[Atholl]

Hi Richard,

It now works perfectly; thanks as always

Atholl