Problem with Worksheet Event

[MaxChef]

Hi all,

This code works for me but it has a bad side effect.

If I attempt to highlight data and "CLEAR CONTENTS", it inserts zeros in the cleared cells. It does the same if I run a macro that clears the entire work range.

Any ideas on how I might fix this?

Also, is it possible to run a Select Case statement using a change event such as this?

Private Sub Worksheet_Change(ByVal Target As Range)
   On Error Resume Next
   If Range("NONFOOD_DATES", "NONFOOD_CHARGES") Is Nothing Then Exit Sub
    On Error GoTo 0

If Not Intersect(Target, Range("NONFOOD_DATES")) Is Nothing Then
  Application.EnableEvents = False
        Target.Offset(0, 1).Value = "Amex-NonFoods"
        Application.EnableEvents = True
End If

If Not Intersect(Target, Range("NONFOOD_CHARGES")) Is Nothing Then
  Application.EnableEvents = False
        Target.Offset(0, 1).Value = "0"
        Application.EnableEvents = True

End If
End Sub

Thanks!

 

[wigi]

Maybe:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Range("NONFOOD_DATES", "NONFOOD_CHARGES") Is Nothing Then Exit Sub
    On Error GoTo 0
    
    If WorksheetFunction.CountA(Target) Then
        Application.EnableEvents = False
        If Not Intersect(Target, Range("NONFOOD_DATES")) Is Nothing Then
            Target.Offset(0, 1).Value = "Amex-NonFoods"
        End If
        If Not Intersect(Target, Range("NONFOOD_CHARGES")) Is Nothing Then
            Target.Offset(0, 1).Value = 0
        End If
        Application.EnableEvents = True
    End If
    
End Sub

 

[MaxChef]

Thanks Wigi!

That works fine.

I would have never considered using the worksheetfunction.COUNTA for the target.

Very good idea.

Thanks.

Mark