I have a workbook with the following code in a sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler
Application.EnableEvents = False
If Target.Count 1 Then Exit Sub
If Right(Target.Address, 2) = "$1" Then Exit Sub
With Me
If Left(Target.Address, 3) = "$A$" Then
With Target.Cells.Offset(0, 1)
.FormulaR1C1 =
"=IF(ISNA(IF(ISBLANK(RC[-1]),"""",VLOOKUP(RC[-1],SAPplantno,
2,FALSE)))," & _
"""Plant Description not found."",
(IF(ISBLANK(RC[-1]),"""",VLOOKUP(RC[-1],SAPplantno,2,FALSE))))"
.Value = .Value
End With
'MsgBox Target.Address & " a"
ElseIf Left(Target.Address, 3) = "$C$" Then
With Target.Cells.Offset(0, 1)
.FormulaR1C1 =
"=IF(ISBLANK(RC[-1]),"""",IF(ISNA(VLOOKUP(RC[-1],matdesclu,2,FALSE)),"
& _
"""Description not found."",
(VLOOKUP(RC[-1],matdesclu,2,FALSE))))"
.Value = .Value
End With
'MsgBox Target.Address & " c"
ElseIf Left(Target.Address, 3) = "$D$" Then
With Target.Cells.Offset(0, -1)
.FormulaR1C1 =
"=IF(ISBLANK(RC[1]),"""",IF(ISNA(VLOOKUP(RC[1],MatNumlu,2,FALSE))," &
_
"""Description not found."",
(VLOOKUP(RC[1],MatNumlu,2,FALSE))))"
.Value = .Value
End With
'MsgBox Target.Address & " d"
ElseIf Left(Target.Address, 3) = "$F$" Then
With Target.Cells.Offset(0, 1)
.FormulaR1C1 =
"=IF(ISNA(IF(ISBLANK(RC[-1]),"""",VLOOKUP(RC[-1],SAPplantno,
2,FALSE)))," & _
"""Plant Description not found."",
(IF(ISBLANK(RC[-1]),"""",VLOOKUP(RC[-1],SAPplantno,2,FALSE))))"
.Value = .Value
End With
'MsgBox Target.Address & " f"
'Else
End If
End With
Application.EnableEvents = True

Exit Sub
errHandler:
MsgBox Err.Number & " " & Err.Description
End Sub

It all works great unless you go back and delete (clearcontents)
something out of multiple cells; then it seems like nothing will
trigger. (before save, before print etc.) No errors, no action. I've
tried manually setting enableevents to true (and in multiple events),
verifying targets and other things but it won't start working again
until I close and re-open the file. I feel like I may be overlooking
something simple. I think it has to do with exiting if the target
count is more than one, but leaving that out produces other
undesirable actions.

I really need to make automation as transparent as possible and limit
interactions/complexity as much as possible to the users.

A somewhat related double post: Whenever I have code in the beforesave
event and close the app with multiple workbooks open, the workbook(s)
won't close even if the code is as simple as me.save. Learned to live
with this one, but if anyone has an explanation, I'd be interested.

Thanks!
ward376