CODEWITHSUNDEEP
vba
CamlCase
CamlCase

Reputation: 191

2013 Excel VBA remove contents of a cell when deleting another

Below is the vba code I am using to auto-populate the date in column 3 when a number is entered in column 1. I need the date to be removed when the number in Column 1 is deleted.

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim A As Range, B As Range, Inte As Range, r As Range
    Set A = Range("A:A")
    Set Inte = Intersect(A, Target)
    If Inte Is Nothing Then Exit Sub
    Application.EnableEvents = False
    For Each r In Inte
    r.Offset(0, 2).Value = Date
    Next r
    Application.EnableEvents = True
    End Sub

Upvotes: 0

Views: 32

Answers (1)

Amit Kohli
Amit Kohli

Reputation: 2950

It was having a problem because the deletion of column 3 triggers another changestate, so I just put something at the top saying if the change isn't in column 1 then don't worry bout it. This should work:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 1 Then
        Dim A As Range, B As Range, Inte As Range, r As Range
        Set A = Range("A:A")
        Set Inte = Intersect(A, Target)
        'If Inte Is Nothing Then Target.Offset(0, 3 - Target.Column).Value = "YES"
        If Target.Offset(0, 1 - Target.Column).Value = "" Then
            Target.Offset(0, 3 - Target.Column).Clear
            Exit Sub
        End If
        'If Inte Is Nothing Then Exit Sub
        Application.EnableEvents = False
        For Each r In Inte
        r.Offset(0, 2).Value = Date
        Next r
        Application.EnableEvents = True
    End If

End Sub

Upvotes: 1

Related Questions