Worksheet change timestamp multiple columns-CodePudding

So I have this case where a specific range in two separate columns are going to be filled out. In either case there should be a date stamped in column F and time stamp in column G. This version I have works to some extend however when you clear out any cell outside of one you get a type mismatch error

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Handler
If Not Intersect(Target, Range("D8:D31,D44:D67,D80:D103,D116:D139")) And Target.Value <> "" 
Then
Application.EnableEvents = False
Target.Offset(0, 2) = Format(Now(), "mm-dd-yyyy")
Target.Offset(0, 3) = Format(Now(), "hh:mm:ss")
Application.EnableEvents = True
Handler:
End If
On Error GoTo Handler2
If Not Intersect(Target, Range("E8:E31,E44:E67,E80:E103,E116:E139")) And Target.Value <> "" 
Then
Application.EnableEvents = False
Target.Offset(0, 1) = Format(Now(), "mm-dd-yyyy")
Target.Offset(0, 2) = Format(Now(), "hh:mm:ss")
Application.EnableEvents = True
Handler2:
End If
End Sub

I assuming this breaks the initial if not logic and that is why its erroring out in that manner.

CodePudding user response：

You can simplify this a little by only intersecting on a single range, and also be able to handle multiple cells being updated:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, c As Range
    
    On Error GoTo haveError
    Set rng = Application.Intersect(Target, _
                 Me.Range("D8:E31,D44:E67,D80:E103,D116:E139"))
      
    If Not rng Is Nothing Then        'any cells of interest updated?
        Application.EnableEvents = False
        For Each c In rng.Cells       'loop over updated cells
            If Len(c.Value) > 0 Then  'has value?
                With c.EntireRow
                    .Columns("F").Value = Format(Now(), "mm-dd-yyyy")
                    .Columns("G").Value = Format(Now(), "hh:mm:ss")
                End With
            End If
        Next c
    End If
    
haveError:
    Application.EnableEvents = True
End Sub