Automatically adding the date time to a cell when another cell is updated AND clearing it when this cell is empty

0 votes

I'm making an excel document for business reports. I took the script from this discussion in the MS Support -  LINK
It appears as follows:

Sub Worksheet_Change(ByVal Target As Range)

 If Not Intersect(Target, Range("A1")) Is Nothing Or _
    Not Intersect(Target, Range("B1")) Is Nothing Or _
    Not Intersect(Target, Range("C1")) Is Nothing Or _
    Not Intersect(Target, Range("D1")) Is Nothing Or _
    Not Intersect(Target, Range("E1")) Is Nothing Then

    Target.Offset(1, 0) = Now
 End If

End Sub

It functions perfectly, but now I want to clear target cells after clearing the main cells. Now, for instance, if I delete A1, the time in A2 remains. I'm really new at Excel and am hoping for some assistance.

Jan 26, 2023 in Others by Kithuzzz
• 38,020 points
435 views

1 answer to this question.

0 votes

You are looking for an IsEmpty check in your code:

If IsEmpty(Target.Value) Then Target.Offset(1, 0).ClearContents Else Target.Offset(1, 0) = Now

As a whole:

Sub Worksheet_Change(ByVal Target As Range)

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

    If IsEmpty(Target.Value) Then Target.Offset(1, 0).ClearContents Else Target.Offset(1, 0) = Now

 End If

End Sub
answered Jan 26, 2023 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
0 answers

How is it possible for DynamoDB to support both Key-Value and Document database properties at the same time

As per DynamoDB's documentation, it supports both ...READ MORE

Apr 5, 2022 in Others by Kichu
• 19,050 points
715 views
0 votes
1 answer

When a row is added to one sheet, automatically add a row to another sheet

Use the VLOOKUP function. Your Spreadsheet 2's first column will ...READ MORE

answered Dec 10, 2022 in Others by narikkadan
• 63,720 points
2,672 views
0 votes
1 answer

VBA How to extract the date and time of arrival of a answered email

Use MailItem.ReceivedTime property. I hope this helps you ...READ MORE

answered Jan 9, 2023 in Others by narikkadan
• 63,720 points
652 views
0 votes
1 answer

Is there a way to test a formula result in excel and type it only once, all within one cell and not using a user defined function?

Use the Let function: =LET(Value,A1+B2+C4+G3+B4,IF(Value>10,"No",Value)) I hope this helps ...READ MORE

answered Jan 9, 2023 in Others by narikkadan
• 63,720 points
624 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,690 points
936 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,720 points
1,940 views
0 votes
1 answer

Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,720 points
1,696 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,720 points
1,236 views
0 votes
1 answer

Excel Formula which places date/time in cell when data is entered in another cell in the same row

Here's how to accomplish things in another ...READ MORE

answered Dec 11, 2022 in Others by narikkadan
• 63,720 points
1,141 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP