Auto update DATE but only if cell is empty

[PartsPig]

I am a first time poster so please be gentle. I have some code I found somewhere that auto populates the date in column C when the neighboring cell in column B is not null. The code is working fine for new entries but when the neighboring cell in column B is changed a week or so later it overwrites column C with the current date. This is a check ledger so the original date must remain. The code I'm using is below and you can see a commented line that I thought would exit the sub if the cell in C was not nothing but it doesn't work. Of course I can just type over the date to correct it but if another user isn't paying attention to the date first before VOIDing a check it gets overwritten. In the example sheet below you don't know which date the VOIDed line should have since the date above and below are different.

'Auto add date to cell when a specific other cell is not null
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range

'If only one cell at a time should be changed, un-comment the next line
If Target.Cells.Count > 1 Then Exit Sub

'To allow deletions to be bypassed without running the code, un-comment the next line
If Target.Cells(1).Value = "" Then Exit Sub

If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub 'specific range

'If Not Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub 'specific range ' I tried this to no avail

'Turn off events to keep out of loops
Application.EnableEvents = False

For Each c In Intersect(Target, Range("B:B"))
If c.Value <> "" Then
Cells(c.Row, "C").NumberFormat = "m/d/yyyy"
Cells(c.Row, "C").Value = Date
End If
Next c

Application.EnableEvents = True
End Sub

Check #	Payee	Date
11923	Bob's	8/15/2024
11924	Gary's	8/15/2024
11925	Jim's	8/15/2024
11926	Kim's	8/15/2024
11927	Steve's	8/15/2024
11928	Gomez's	8/16/2024
11929	Freeman's	8/16/2024
11930	Alpha's	8/19/2024
11931	Nathan's	8/19/2024
11932	John's	8/19/2024
11933	Anna's	8/19/2024
11934	Lilly's	8/19/2024
11935	VOID	9/13/2024
11936	Norm's	8/20/2024

 

[Candyman8019]

Try something like this:

'Auto add date to cell when a specific other cell is not null
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range

'If only one cell at a time should be changed, un-comment the next line
If Target.Cells.Count > 1 Then Exit Sub

'To allow deletions to be bypassed without running the code, un-comment the next line
If Target.Cells(1).Value = "" Then Exit Sub

If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub 'specific range

If Target.Offset(0, 1).Value <> "" Then Exit Sub

'Turn off events to keep out of loops
Application.EnableEvents = False

For Each c In Intersect(Target, Range("B:B"))
If c.Value <> "" Then
Cells(c.Row, "C").NumberFormat = "m/d/yyyy"
Cells(c.Row, "C").Value = Date
End If
Next c

Application.EnableEvents = True
End Sub

 

[PartsPig]

That worked perfectly. One more question, now that I can type VOID without the date changing I am wondering how to put that VOID in columns D E F G of the same row through code?

 

[Candyman8019]

What column would you type VOID in...B? If so, try this:

'Auto add date to cell when a specific other cell is not null
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range

'If only one cell at a time should be changed, un-comment the next line
If Target.Cells.Count > 1 Then Exit Sub

'To allow deletions to be bypassed without running the code, un-comment the next line
If Target.Cells(1).Value = "" Then Exit Sub

If Not Intersect(Target, Range("B:B")) Is Nothing Then

    'Turn off events to keep out of loops
    Application.EnableEvents = False
    
    'if value in B = VOID then D:G will also be void.
    If Target.Value = "VOID" Then
        Target.Offset(0, 2).Value = "VOID"
        Target.Offset(0, 3).Value = "VOID"
        Target.Offset(0, 4).Value = "VOID"
        Target.Offset(0, 5).Value = "VOID"
    End If
    
    If Target.Offset(0, 1).Value <> "" Then GoTo SkipDate

    For Each c In Intersect(Target, Range("B:B"))
        If c.Value <> "" Then
        Cells(c.Row, "C").NumberFormat = "m/d/yyyy"
        Cells(c.Row, "C").Value = Date
        End If
    Next c
End If
    
:SkipDate:
Application.EnableEvents = True
End Sub

 

[PartsPig]

I put the following to propagate the VOID across the necessary cells...

    'Propagate VOID to other columns
    If Target.Offset(0, 1).Value <> "" Then
        If Target.Offset(0, 0).Value = "VOID" Then
            For x = 2 To 4
                Target.Offset(0, x) = "VOID"
            Next x
        End If
        Exit Sub
    End If

 

[PartsPig]

Use Candyman8019's version above instead of mine as his cares for turning the application events off and back on properly

 

[Candyman8019]

Use Candyman8019's version above instead of mine as his cares for turning the application events off and back on properly

You can still use the for next loop if you like…I figured it’s only 4 cells so I’d list them each. Both ways work.

 

[PartsPig]

This is the final version I settled on is below. I think the only differences are the for next loop, the location of turning off the events and I had to remove the first colon from ":SkipDate:". It just seemed to make sense for the events "off and on" commands to be outside the if .. end if to me. If there was a reason it was located where Candyman put it someone please let me know.

'Auto add date to cell when a specific other cell is not null
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range

    'If only one cell at a time should be changed, un-comment the next line
    If Target.Cells.Count > 1 Then Exit Sub

    'To allow deletions to be bypassed without running the code, un-comment the next line
    If Target.Cells(1).Value = "" Then Exit Sub
        
    'Turn off events to keep out of loops
    Application.EnableEvents = False
    
    'Don't overwrite existing Date when voiding checks or correcting Payee column
    If Not Intersect(Target, Range("B:B")) Is Nothing Then 'specific range
        
        'Propagate VOID to other columns
        If Target.Value = "VOID" Then
            For x = 2 To 6
                Target.Offset(0, x).Value = "VOID"
            Next x
        End If
    
        If Target.Offset(0, 1).Value <> "" Then GoTo SkipDate
    
        'Add Date
        For Each c In Intersect(Target, Range("B:B"))
            If c.Value <> "" Then
                Cells(c.Row, "C").NumberFormat = "m/d/yyyy"
                Cells(c.Row, "C").Value = Date
            End If
        Next c
    End If
    
SkipDate:
    'Turn events back on
    Application.EnableEvents = True
End Sub

 

[Candyman8019]

Yup. The extra colon was a typo on my part. Good catch.
As for the placement of the enableevents…either will be just fine. The approach I took is of the user didn’t edit a cell in column B then don’t bother disabling the events.