CODEWITHSUNDEEP
excelvbaformula
James B
James B

Reputation: 159

Clear contents of cell if another changes to blank

I have an excel sheet where if a value is entered into a cell in column A, then the cell next to it in column B automatically generates the date and time. The problem I'm having is I want a check where if the value in column A is blank i.e. "" then the date and time is cleared too. I have figured out how to add the date and time just not how to add a check on the value of cell to see if it is blank. The code is below along with an example;

Example;

A4 a change is made, the current date and time is entered into B4 
A8 a change is made, the current date and time is entered into B8
A4 the user clears the cell (presses delete on their keyboard), B4 is cleared too.
A4 the user enters "hello world", the current date and time is entered again

Code;

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
xCellColumn = 2
xTimeColumn = 5
xRow = Target.Row
xCol = Target.Column
If Target.Text <> "" Then
    If xCol = xCellColumn Then
       Cells(xRow, xTimeColumn) = Now()
    End If
End If
End Sub

Thanks

Upvotes: 2

Views: 763

Answers (1)

Vityata
Vityata

Reputation: 43575

This works only for column A and column B:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

    Application.EnableEvents = False
    If Target = vbNullString Then
        Target.Offset(0, 1) = vbNullString
    Else
        Target.Offset(0, 1) = Now
    End If

    Application.EnableEvents = True

End Sub

If you want to make it work for any 2 columns, then remove this line:

If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

The command Application.EnableEvents = True is used to make sure that the _Change event is not called once the Sub changes a cell.

Upvotes: 2

Related Questions