VBA AutoDate: Clearing Autodate after information is deleted from cells

[SBC]

My spreadsheet layout is: A col is where labels are scanned in, B col is where shipping labels are scanned in, C Col is where the autodate feeds into.

Individual labels are scanned in until the container is full then click over or arrow over to B col to scan in the shipping label at which point the autodate is filled in col C via VBA.

My issue is that if someone forgets to switch the active cell back over they start scanning in labels again and don't pay attention they end up with 4 or 5 labels in the wrong column so what they do is delete them then rescan them. The autodate will not delete for all the entries and i get a Run-time error '1004': Application defined or object defined error

Private Sub Worksheet_Change(ByVal Target As Range)
' Auto Date
ActiveSheet.Unprotect
Dim Cell As Range
For Each Cell In Target
    If Cell.Column = Range("B:B").Column Then
        If Cell.Value <> "" Then
        Cells(Cell.Row, "C").Value = Now
        
        Else
            Cells(Cell.Row, "C").Value = ""
        End If
    End If
If Cells(Cell.Row, "C").Value <> "" Then
ActiveWorkbook.Save


End If

Next Cell

ActiveSheet.Protect
End Sub

As you can see i have a couple other things going on as well, unprotecting the sheet so the autodate can fill in a protected cell, as well as saving once a cell in col C has been filled, then reprotecting the sheet after all everything is completed.

As i think about it as i type could this protecting/unprotecting be my issue?

I have no issue with the autodate feeding in nor do i have an issue with deleting 1 cell in col B, its only when i delete multiples in col B that it wont clear all the date stamps in C and throws the error up

Basic knowledge here so please explain your answer throughly

 

[JLGWhiz]

I am not sure how you are scanning the data into column B but give this a try and see if it fixes the problem. it replaces current code.

Private Sub Worksheet_Change(ByVal Target As Range)
' Auto Date
ActiveSheet.Unprotect
    If Not Intersect(Target, Columns("B")) Is Nothing Then
        Target.Offset(, 1).Value = Now
    Else
        Target.Offset(, 1).Value = ""
    End If
ActiveWorkbook.Save
ActiveSheet.Protect
End Sub

 

[SBC]

Sorry its taken so long to reply, been traveling and have not had the time to spend on this side project. This is the error its throwing up at me when i run this

If i run debug it shows Target.Offset(, 1).Value = "" in yellow then crashes out

 

[JLGWhiz]

The message says it does not like the value property in the statement so let's try without "value". Let Excel decide what it is.

Private Sub Worksheet_Change(ByVal Target As Range)
' Auto Date
ActiveSheet.Unprotect
    If Not Intersect(Target, Columns("B")) Is Nothing Then
        Target.Offset(, 1) = Now
    Else
        Target.Offset(, 1) = ""
    End If
ActiveWorkbook.Save
ActiveSheet.Protect
End Sub

 

[SBC]

same outcome only it says "_Default" of object 'Range' failed

When i enter a number into column B and hit enter it pops up the blue circle of thinking for a good 3-5 seconds before it pops up the error

 

[JLGWhiz]

One thing it needs is to disable the event trigger while it is posting in column C. See if you still get the message with this modified version

Private Sub Worksheet_Change(ByVal Target As Range)
' Auto Date
ActiveSheet.Unprotect
[COLOR=rgb(85, 57, 130)]Application.EnableEvents = False[/COLOR]
    If Not Intersect(Target, Columns("B")) Is Nothing Then
        Target.Offset(, 1) = Now
    Else
        Target.Offset(, 1) = ""
    End If
ActiveWorkbook.Save
[COLOR=rgb(85, 57, 130)]Application.EnableEvents = True[/COLOR]
ActiveSheet.Protect
End Sub