How to Auto Populate Date in Excel When Cell Is Updated

Get FREE Advanced Excel Exercises with Solutions!

Sometimes, several people work on a worksheet and anyone can update the worksheet anytime. So, the worksheet owner might want to see the last updates with the update date and time. In this article, we will show you how to auto-populate date in Excel when a cell is updated. Here, we will use the Excel VBA to describe the process.

Let’s say, we have a dataset (B4:C11) in Excel. Here, we will put some value in a cell of column B and then the adjacent column C cell will show the date and time of changing the cell. In this article, we will see two examples of using Excel VBA to auto populate the current date in Excel when a cell is updated. So, without further delay, let’s get started.

Table of Contents hide

1. Applying Excel VBA to Auto Populate Date When Cell Is Updated

2. Using VBA Macro to Auto Populate Date in Excel When Specific Cell Is Updated

Conclusion

1. Applying Excel VBA to Auto Populate Date When Cell Is Updated

VBA is a programming code that creates macros to perform simple or complicated Excel activities. In this method, we will apply Excel VBA to auto populate dates in the entire column B of the above dataset (B4:C11). We need the Developer tab to write VBA code. Follow the steps below to import the Developer tab to the ribbon and then apply the VBA code to auto populate dates.

Steps:

First, check out whether your worksheet is showing the Developer tab in the tab list.
However, we can not see the Developer tab in the tab list of the following image.

To bring the Developer tab in the ribbon, first of all, go to the File tab.

Secondly, choose Options from the left side menu.

In turn, the Excel Options dialog box will appear.
Eventually, click on Customize Ribbon located at the left side of the dialog box.

Then, on the right side of the dialog box choose Main Tabs in the Customize the Ribbon dropdown.
Next, in the Main Tabs section, you will see the Developer option is unchecked.
Therefore, check the box.
Click OK.
See the screenshot for a better understanding.

After that, we will see the Developer tab in the tab list.

Now, to open the VBA editor, go to the Developer tab > find Code group > select Visual Basic (see screenshot).

Hence, the Microsoft VBA code editor will open like the following image.
Besides, we can also use the keyboard shortcut Alt + F11 to open this window.

Afterward, to open the Sheet2 worksheet (named as Entire Column) related code editor, double-click on Sheet2 (Entire Column) on the left side of the editor.

Thereupon, click the drop-down part of the (General) and choose Worksheet.
When you select Worksheet, all worksheet related pre-built functions will be available to you.

In the same way, from the right-side tab, choose the Change option.

At this time, you will find that two codes are appearing in the code editor.

Select the lower part code (see screenshot).

Press the delete key on the keyboard.
Consequently, the code editor will show the following two lines code shown in the picture below:

Accordingly, insert the VBA code below between these two lines:

' Auto Date
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
Next Cell

We can see the final look of the VBA code in the image below.

From the VBA code, we can see that it is applicable for the entire B column.
Forthwith, click on the Save button located at the upper part of the code window.

You will see the following Microsoft message (see screenshot).
This message will show if your workbook is saved with .xlsx extension instead of .xlsm extension.
As your workbook now has VBA code, you can not save it with the .xlsx extension.
So, select No as we want to save the workbook as a macro-enabled file type.

Momentarily, choose Excel Macro-Enabled Workbook option from the Save as type menu.

Thus, click on the Save button to save the file newly.

Return to the worksheet and type anything you want in any cell of column B.
In our case, we typed John in cell B5.

Press the Enter key and then you will get the current date & time in the adjacent cell C5.

Check it out for the other cells of column B.

Finally, we can see that the VBA code auto populates the current date & time in the entire column B (see screenshot).

Similar Readings

2. Using VBA Macro to Auto Populate Date in Excel When Specific Cell Is Updated

This time we are going to show how to auto populate dates of some specific cells (B5:B8), not the entire column. Look at the following dataset (B5:B8). Say, in this worksheet, people can only update data on cell range B5:B8 and adjacent cells on column C will record the date of last changes. The process of this method is almost similar to method 1, the only difference is in the VBA code. See the following steps to apply the VBA code of this method.

Steps:

To begin, go to the Developer tab > Code group > click on Visual Basic.

As a result, the VBA code window will appear.
Next, double-click on the worksheet named Sheet3 (Specific Cells).
After that, the VBA code editor will look like the picture below.

Eventually, select Worksheet from the dropdown of the (General) box.

Then, choose Change from the dropdown options of SelectionChange (located at the right-side of the code window).

At this time, select the second code in the code window (see screenshot).

Therefore, delete the selected code.
Eventually, keep your cursor in between the two lines code shown in the screenshot below:

Later, insert the VBA code below between the two lines (see screenshot):

With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("B5:B8"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With

The VBA code shows that we can only update the specific range B5:B8 in column B.
Lastly, click on the Save button in the code window.

To check the VBA code, enter any data (Merry) in cell B5.

After pressing Enter, you will get the timestamp in the adjacent cell C5.

Moreover, enter data in another cell (B7) in the B5:B8 range.
Again, you will get the output in the adjacent cell (C7).

But, if you enter data in a cell (B10) out of the range (B5:B8) specified in the VBA code, it will not return any date.
We can see the blank cell (C10) adjacent to cell B10 in the screenshot below.

Download Practice Workbook

Download the practice workbook from here.

Auto Populating Date.xlsm

Conclusion

I hope this article will be helpful for you to auto populate dates in excel when the cell is updated. Download the practice workbook and give it a try. Let us know your feedback in the comment section. Follow our website ExcelDemy to get more articles like this.

Private Sub Worksheet_Change(ByVal Target As Range) Dim Cell As Range For Each Cell In Target If Cell.Column = Range("A:A").Column Then If Cell.Value <> "" Then Cells(Cell.Row, "C").Value = Now Else Cells(Cell.Row, "C").Value = "" End If ElseIf Cell.Column = Range("K:K").Column Then If Cell.Value <> "" Then Cells(Cell.Row, "L").Value = Now Else Cells(Cell.Row, "L").Value = "" End If End If Next Cell End Sub

Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Count > 1 Then Exit Sub If Not Intersect(Range("D3:D22"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub

Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Count > 1 Then Exit Sub If Not Intersect(Range("B3:I3"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(1, 0).ClearContents Else With .Offset(1, 0) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub

Private Sub Worksheet_Change(ByVal Target As Range) Dim Cell As Range For Each Cell In Target If Cell.Column = Range("A:A").Column Then If Cell.Value <> "" Then Cells(Cell.Row, "M").Value = Now Else Cells(Cell.Row, "M").Value = "" End If ElseIf Cell.Column = Range("B:B").Column Then If Cell.Value <> "" Then Cells(Cell.Row, "M").Value = Now Else Cells(Cell.Row, "M").Value = "" End If ElseIf Cell.Column = Range("C:C").Column Then If Cell.Value <> "" Then Cells(Cell.Row, "M").Value = Now Else Cells(Cell.Row, "M").Value = "" End If End If Next Cell End Sub

Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Count > 1 Then Exit Sub If Not Intersect(Range("C5:C8"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -1).ClearContents Else With .Offset(0, -1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub

Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Count > 1 Then Exit Sub If Not Intersect(Range("D5:D8"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" And Target.Value <> "" Then ActiveSheet.Protect Contents:=False Range(Target.Address).Locked = True ActiveSheet.Protect Contents:=True End If End Sub

Private Sub Worksheet_Change(ByVal Target As Range) 'Autopupulate Cells with date depending on particular cell value Dim Cell As Range For Each Cell In Target If Cell.Column = Range("D:D").Column Then If Cell.Value = "Start" Then Cells(Cell.Row, "G").Value = Now ElseIf Cell.Value = "In-Progress" Then Cells(Cell.Row, "H").Value = Now ElseIf Cell.Value = "Pending" Then Cells(Cell.Row, "I").Value = Now ElseIf Cell.Value = "Completed" Then Cells(Cell.Row, "J").Value = Now End If ' You can add additional cases if you want ' Just follow the above method and change the cell reference ElseIf Cell.Column = Range("G:J").Column Then If Cells(Cell.Row, "D").Value = "" Then If Cell.Column = Range("G:G").Column Then Cells(Cell.Row, "G").Value = "" ElseIf Cell.Column = Range("H:H").Column Then Cells(Cell.Row, "H").Value = "" ElseIf Cell.Column = Range("I:I").Column Then Cells(Cell.Row, "I").Value = "" ElseIf Cell.Column = Range("J:J").Column Then Cells(Cell.Row, "J").Value = "" End If End If End If Next Cell ' You may want to clear columns (G:J), if cell value is changed End Sub

Private Sub Worksheet_Change(ByVal Target As Range) Dim cell As Range If Not Intersect(Target, Range("O:V")) Is Nothing Then For Each cell In Intersect(Target, Range("O:V")).Cells If cell.Row <= 400 Then Range("N" & cell.Row).Value = Date End If Next cell End If End Sub

Private Sub Worksheet_Change(ByVal Target As Range) Dim Cell As Range For Each Cell In Target If Cell.Column = Range("C:C").Column Then If Cell.Value <> "" Then Cells(Cell.Row, "A").Value = Date Cells(Cell.Row, "B").Value = Time Else Cells(Cell.Row, "A").Value = "" Cells(Cell.Row, "B").Value = "" End If End If Next Cell End Sub

40 Comments

George Jun 20, 2018 at 10:03 PM

I altered the code to produce different results: Putting the Date in column E and the Time in column F. I am just wondering if there’s a better or shorter way to get this same results?

My revision:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range(“D3:D22”), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = “dd mmm yyyy”
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
‘Second Function ****
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range(“D3:D22”), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 2).ClearContents
Else
With .Offset(0, 2)
.NumberFormat = “hh:mm:ss”
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub
- Reply
  Kawser Jun 21, 2018 at 12:28 PM
  
  I will check out this code. Thanks for the input.
anil Jan 28, 2019 at 2:56 AM

when we normally use as range object we also use set = “something here”. In the very first code above when we declare cell as range we did not use the set keyword. I did not understand why.Can you please explain it to me.

Private Sub Worksheet_Change(ByVal Target As Range)
‘ Auto Date
Dim Cell As Range
- Reply
  Md. Shamim Reza Aug 31, 2022 at 3:17 PM
  
  Hi Anil,
  
  You are right. We normally use the “Set statement” to assign range objects. But it is done here indirectly without the Set statement.
  
  This is a Change event. Here, “Target” indicates to all cells within the sheet. So the “For Each Cell In Target” statement works as the alternative to the Set statement.
  
  Hope this clarifies your confusion. And thank you for reaching out to us.
  
  Regards
  Md. Shamim Reza (ExcelDemy Team)
ammar May 1, 2019 at 11:13 PM

can I use 2 time stamps in the same excel sheet
input data in column “A” auto date in Column “C”
input data in column “K” auto date in Column “L”

code used:

Private Sub Worksheet_Change(ByVal Target As Range)
‘ Auto Date
Dim Cell As Range
For Each Cell In Target
If Cell.Column = Range(“D:D”).Column Then
If Cell.Value “” Then
Cells(Cell.Row, “B”).Value = Now
Else
Cells(Cell.Row, “B”).Value = “”
End If
End If
Next Cell
End Sub
End Sub
- Reply
  Md. Shamim Reza Sep 4, 2022 at 1:54 PM
  You can check the following code for that. Just copy the ElseIf statement for more columns.
```
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
For Each Cell In Target
If Cell.Column = Range("A:A").Column Then
If Cell.Value <> "" Then
Cells(Cell.Row, "C").Value = Now
Else
Cells(Cell.Row, "C").Value = ""
End If
ElseIf Cell.Column = Range("K:K").Column Then
If Cell.Value <> "" Then
Cells(Cell.Row, "L").Value = Now
Else
Cells(Cell.Row, "L").Value = ""
End If
End If
Next Cell
End Sub
```
  Thanks for reaching out to us. Keep in touch.
  
  Regards
  Md. Shamim Reza (ExcelDemy Team)
Wayne Edmondson Jul 31, 2019 at 9:36 AM

Hi Kawser.. cool solution.. like your code. Another way to solve this is with a worksheet formula as follows:
=IF(A2=””,””,IF(B2″”,B2,NOW()))
The above assumes your data will go in column A, starting with A2 and the date/time stamp will go in column B, starting with B2. For this to work, you must enable Iterative Calculation in the Options=>Formulas=>Calculations options dialog. Just copy the formula down column B for as far as you want the stamp to work. While VBA can be more flexible, the above is a good way to get it done with a cell formula and an option change. I hope you like it. Thanks again for all of your tutorials.. super helpful and interesting.. makes me better at EXCEL, every time you send them. Thumbs up!
- Reply
  Sabrina Ayon Oct 2, 2022 at 1:02 PM
  
  You are most welcome, Wayne Edmondson!
  Stay Tuned!
  
  Regards,
  Sabrina Ayon
  Author, ExcelDemy.
Wayne Edmondson Jul 31, 2019 at 9:39 AM

Not sure what happened above to the formula. I think the editor can’t show the less than / greater than symbols. The formula should be:
=IF(A2=””,””,IF(B2 less than symbol and greater than symbol “”,B2,NOW()))
Hope that helps..
- Reply
  Sabrina Ayon Oct 2, 2022 at 1:00 PM
  
  Hello, WAYNE EDMONDSON!
  Thanks for sharing your thoughts with us!
  Stay Tuned!
  
  Regards,
  Sabrina Ayon
  Author, ExcelDemy.
Tori E Nov 3, 2019 at 4:43 AM

Is there a way to auto-populate a date based on a specific day rather than current day? For example auto-populate 60 days from August 28, 2019 rather than 60 days from current date?
Vickie Watt Oct 31, 2020 at 10:32 AM

Thanks for always posting valuable info. I don’t know VBA, but in Excel NOW is volatile. If you insert it as described above, is the date static?
- Reply
  Sabrina Ayon Oct 2, 2022 at 3:02 PM
  
  Hello, VICKIE WATT!
  Thanks for your comment. Yes, this is date static.
Darryl Jan 23, 2021 at 5:21 AM

Interested in this same function – but for ROWS vs. columns. We have a shared spreadsheet that needs to be updated by 20+ folks, and want to capture the date ANY TIME a field changes in the ROW. We would allocate column A to be the date field that would get updated if any cell in the row from B:AZ (or however big the range you want). Can’t seem to find this capability.
Robbie Feb 2, 2021 at 8:51 PM

Hi,
Thanks for this code it is very close to what I am looking for. I need to change the code to capture the date when one of seven different fields is updated. I have changed the range to include all the columns, but it still only works for the first column. Does anyone know what I am doing wrong?

Thanks

Private Sub Worksheet_Change(ByVal Target As Range)

‘ Auto Date
Dim Cell As Range
For Each Cell In Target
If Cell.Column = Range(“J:J”).Column Then
If Cell.Value “” Then
Cells(Cell.Row, “Q”).Value = Now
Else
Cells(Cell.Row, “Q”).Value = “”
End If
End If
Next Cell

End Sub
Steve Hayward Feb 16, 2021 at 3:14 AM

Great solution perfectly explained Well done sir!

One mod I’m not seeing… I have a short column of cells (B20:B29), which, if one changes, they all change. albeit they may very well hold the same value. Adjacent cells values (C20:I29) will change.

Code works fine however, I only need one date cell currently in B18

Modifying your code in part to read:
If Not Intersect(Range(“B20”), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(-2, 0).ClearContents
Else
With .Offset(-2, 0)
changes the date in that cell, but only if attributed to B20 else it overwrites successively the values in B .Offset(-2, 0).

I’m not so clever in VB I can figure out a solution.

Any offers?
Greg J Pickett Feb 24, 2021 at 3:40 AM

Hi Kawser,
Great code for udating the date.
One thing that I would like to see (how to do it) is to have the date change every time the adjacent cell changes.
Some 4500 rows of data and the status column (lets say N:N) can change between 5 different listings. I do use the Filter Function to generate a report, however, if the date is changed by VBA (my code) the #Spill will not occur in the report sheet. Adding the date manually allows the SPILL.
My code I have is:

Private Sub Worksheet_Change(ByVal Target As Range)
‘Auto Date input when adjacent cell changes.
Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
Dim xDPRg, xRg As Range
xCellColumn = 14
xTimeColumn = 15
xRow = Target.Row
xCol = Target.Column
If Target.Text “” Then
If xCol = xCellColumn Then
Cells(xRow, xTimeColumn) = Now()
Else
On Error Resume Next
Set xDPRg = Target.Dependents
For Each xRg In xDPRg
If xRg.Column = xCellColumn Then
Cells(xRg.Row, xTimeColumn) = Now()
End If
Next
End If
End If
End Sub
Just hoping someone may know the answer.
Cheers
- Reply
  Kawser Feb 24, 2021 at 6:06 PM
  
  Hi Greg,
  That is really good input from you. Hope your piece of code will help people who are in trouble. Cheers!
  Best regards
Daniel B May 26, 2021 at 7:27 PM

Thanks for this code!
When I run it the date isn’t input into the adjacent cell until I go back up to the cell.
Do you know any fix for this?
Megan Jun 17, 2021 at 8:55 PM

How do you add multiple codes for the same sheet? I want to auto-date multiple columns, not just 1.
hannah Nov 21, 2021 at 6:12 PM

Hi,
when im closing the file and open it again, the function is not working.
you know maybe why ?
i saved it as enabled workbook
Andy Feb 5, 2022 at 5:22 AM

The only thing I don’t like about this is that “undo” no longer works on the cells that are being monitored. Is there any way to change that?? Otherwise, this was very helpful, thank you. As info: For my specific needs, I wanted to check multiple columns, so I just repeated the first solution. Not at all elegant but it worked. Also, I didn’t want the date to get deleted if a cell changed back to empty, so I changed the ELSE result to also be = Now.

Like this:

Private Sub Worksheet_Change(ByVal Target As Range)
‘ Auto Date column L if any cell in columns B thru K are modified
Dim Cell As Range

For Each Cell In Target
If Cell.Column = Range(“B:B”).Column Then
If Cell.Value “” Then
Cells(Cell.Row, “L”).Value = Now
Else
Cells(Cell.Row, “L”).Value = Now
End If
End If
Next Cell

For Each Cell In Target
If Cell.Column = Range(“C:C”).Column Then
If Cell.Value “” Then
Cells(Cell.Row, “L”).Value = Now
Else
Cells(Cell.Row, “L”).Value = Now
End If
End If
Next Cell

For Each Cell In Target
If Cell.Column = Range(“D:D”).Column Then
If Cell.Value “” Then
Cells(Cell.Row, “L”).Value = Now
Else
Cells(Cell.Row, “L”).Value = Now
End If
End If
Next Cell

End Sub
- Reply
  Kawser Feb 6, 2022 at 11:47 AM
  
  Thanks a lot of your input, Andy!
Abby Mar 4, 2022 at 10:18 PM

Is there a way to implement this without the timestamp being included in the date? Not just formatting, just in the formula bar as well.
- Reply
  Osman Goni Ridwan Sep 1, 2022 at 12:49 PM
  Hello ABBY!
  You have to change a line in the VBA code that is defining the format of the output.
  
  Insert this line:
  .NumberFormat = “dd mmm yyyy” instead of this .NumberFormat = “dd mmm yyyy hh:mm:ss”
  
  So, the code will become as follows-
```
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("D3:D22"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub
```
Arif Apr 14, 2022 at 7:16 PM

Good explanation. Thanks.
However, I should like to adapt this to get the dates in a row instead of in a column.

Would appreciate any suggestions. Thanks.
- Reply
  Osman Goni Ridwan Sep 1, 2022 at 1:46 PM
  Hello ARIF, to get the dates in the next row use the following code when you will insert values in cells of range B3:I3 and want to auto-populate date and time in cell range B4:I4.
  
  CODE:
```
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
            If .Count > 1 Then Exit Sub
            If Not Intersect(Range("B3:I3"), .Cells) Is Nothing Then
                Application.EnableEvents = False
                If IsEmpty(.Value) Then
                    .Offset(1, 0).ClearContents
                Else
                    With .Offset(1, 0)
                        .NumberFormat = "dd mmm yyyy hh:mm:ss"
                        .Value = Now
                    End With
                End If
                Application.EnableEvents = True
            End If
        End With
End Sub
```
  You can change the cell range as your want. I hope, your problem will be solved in this way. If not, please share the Excel file and send us the problem with a little more explanation in an email at [email protected]
Lupe May 19, 2022 at 9:11 AM

How can you edit this code to check 3 columns or more for data entry instead of the 1?
- Reply
  Md. Shamim Reza Sep 4, 2022 at 1:38 PM
  Hi
  
  You can check the following code for that. Just copy the ElseIf statement for more columns.
```
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
For Each Cell In Target
    If Cell.Column = Range("A:A").Column Then
        If Cell.Value <> "" Then
            Cells(Cell.Row, "M").Value = Now
        Else
            Cells(Cell.Row, "M").Value = ""
        End If
    ElseIf Cell.Column = Range("B:B").Column Then
        If Cell.Value <> "" Then
            Cells(Cell.Row, "M").Value = Now
        Else
            Cells(Cell.Row, "M").Value = ""
        End If
    ElseIf Cell.Column = Range("C:C").Column Then
        If Cell.Value <> "" Then
            Cells(Cell.Row, "M").Value = Now
        Else
            Cells(Cell.Row, "M").Value = ""
        End If
    End If
Next Cell
End Sub
```
  Thanks for reaching out to us. Keep in touch.
  
  Regards
  Md. Shamim Reza (ExcelDemy Team)
Gia Sep 30, 2022 at 3:01 AM

Hello,

I am following the auto date. I am just wondering how do I do it if I would like another column to act the same.

For example, I fill out Column C with data, and Column B will automatically update with the date when Column C was filled out.

How will I do it if I would like to have Column E to automatically update with the date and time when I fill Column D with “Delivered”

I hope it does make sense. I tried following some pointers here and putting two and two together. But I can’t seem to make it work. Thanks so much!
- Reply
  Sabrina Ayon Oct 3, 2022 at 10:29 AM
  Hello, GIA!
  As you mentioned, you fill out Column C with data, and Column B will automatically update with the date when Column C was filled out. All you need to do is change the range in your code, and also change the reference argument which is the offset. Try this code below.
```
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
        If .Count > 1 Then Exit Sub
        If Not Intersect(Range("C5:C8"), .Cells) Is Nothing Then
            Application.EnableEvents = False
            If IsEmpty(.Value) Then
                .Offset(0, -1).ClearContents
            Else
                With .Offset(0, -1)
                    .NumberFormat = "dd mmm yyyy hh:mm:ss"
                    .Value = Now
                End With
            End If
            Application.EnableEvents = True
        End If
    End With
End Sub
```
  Also, you can use the same code for column E to automatically update with the date and time when you fill Column D with “Delivered”. You just have to change the range.
```
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
        If .Count > 1 Then Exit Sub
        If Not Intersect(Range("D5:D8"), .Cells) Is Nothing Then
            Application.EnableEvents = False
            If IsEmpty(.Value) Then
                .Offset(0, 1).ClearContents
            Else
                With .Offset(0, 1)
                    .NumberFormat = "dd mmm yyyy hh:mm:ss"
                    .Value = Now
                End With
            End If
            Application.EnableEvents = True
        End If
    End With
End Sub
```
  Please follow the instructions of the method I linked down.
  
  https://www.exceldemy.com/auto-populate-date-in-excel-when-cell-is-updated/#2_Auto_Populate_Dates_in_Some_Specific_Cells_While_Updating_with_Excel_VBA
  
  Hope this will help you!
  Best Regards.
John Oct 21, 2022 at 1:36 AM

I am using this code for auto dating, I am trying to add a way to lock down the row so no changes can be made after they sign and it provides the date.
Dim Cell As Range
For Each Cell In Target
If Cell.Column = Range(“G5:G300”).Column Then
If Cell.Value “” Then
Cells(Cell.Row, “E”).Value = Now
Else
Cells(Cell.Row, “E”).Value = “”
End If
End If
Next Cell
John john Oct 21, 2022 at 3:39 AM

I am trying to add coding that will lock the row after the date auto updates. Then I want it to create an audit log onto another sheet.
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
Next Cell
- Reply
  Sabrina Ayon Oct 23, 2022 at 3:24 PM
  Hello, JOHN!
  Thanks for your comment!
  You can lock the row after the date auto updates with the following code.
```
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And Target.Value <> "" Then
    ActiveSheet.Protect Contents:=False
    Range(Target.Address).Locked = True
    ActiveSheet.Protect Contents:=True
End If
End Sub
```
  A cell should only be locked if cell A1 was updated and it is not blank, according to this formula: if Target.Address = “$A$1” and Target.Value > “”
  Just substitute the relevant cell value for $A$1 to make the macro function on cell B1, cell D15, or any other cell. For this to function, the column and row references must be preceded by dollar signs.
  By changing > “” in the line above to = “desired value,” you may additionally lock the cell only if a certain value was entered, allowing you to do things like lock the cell only if OK was entered or anything similar.
  
  Hope this will help you!
  Good Luck!
  
  Regards,
  Sabrina Ayon
  Author, ExcelDemy.
Geenie Mar 6, 2023 at 4:07 AM

Hello,

I am working with this codes for my project for a while, and I am able to make some work, the way I like it to. However, what I am trying to achieve is this.

Column D will have updatable information like stages in an entire process. Ex. “Start” “In-Progress”, “Pending”, “Completed”, and so on.

What I want to happen is have the Dates for each stages will be stamped on different columns. For example,

When Column D is set to “Start”, Column G will have the date, then if Column D is updated to “In-Progress” Column H will have the date… so on and so forth.

Thanks so much!

Md. Abu Sina Ibne Albaruni Mar 9, 2023 at 1:33 PM

Hi,

Thank you for your comment. From the problem you have stated, it looks like you need to just modify the previous code a little bit. You can use the following code to accomplish your task.

Regards,
Md. Abu Sina Ibne Albaruni
Team ExcelDemy

Heidi Mar 23, 2023 at 5:46 AM

I have a similar problem I am trying to solve, but don’t see that it’s been asked. I have a spreadsheet with 400 rows and want to have column N automatically update with today’s date when anything is changed in the row for columns O:V.
- Reply
  Aniruddah Alam Mar 27, 2023 at 3:23 PM
  Thank you Heidi for your comment. You can paste the following VBA code into your Desired Worksheet on the VBA window. This code will automatically update the current date in the corresponding cell of column N if anything is changed in columns O:V. Hope, it will solve your problem. If you have any further queries, you can post them on our Exceldemy Forum.
  
  Regards
  Aniruddah
  Team Exceldemy
```
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    If Not Intersect(Target, Range("O:V")) Is Nothing Then
        For Each cell In Intersect(Target, Range("O:V")).Cells
            If cell.Row <= 400 Then
                Range("N" & cell.Row).Value = Date
            End If
        Next cell
    End If
End Sub
```
Jeremy Hock Jun 15, 2023 at 12:51 PM

How would I write this so that when data is entered into column C date populates in Column A and time populates in column B?
- Reply
  Shahriar Abrar Rafid Jun 18, 2023 at 11:59 AM
  Hello JEREMY,
  Thank you for your comment. I’ve understood your problem. You can use the following instruction. Don’t paste this code into the module. Use it in the sheet module.
  Right-click on the sheet name and select the View Code option from the context menu.
  
  Besides, you can double-click on the specific sheet to add a module for this sheet especially.
  
  In the module, paste the following code.
```
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
For Each Cell In Target
    If Cell.Column = Range("C:C").Column Then
        If Cell.Value <> "" Then
            Cells(Cell.Row, "A").Value = Date
            Cells(Cell.Row, "B").Value = Time
        Else
            Cells(Cell.Row, "A").Value = ""
            Cells(Cell.Row, "B").Value = ""
        End If
    End If
Next Cell
End Sub
```
  You don’t have to run this code. Simply, save it and return to the worksheet and it’ll work smoothly.
  If you delete the data in Column C, the date and time for this particular data will be erased also. But, don’t delete the entire column, it’ll create an error and you have to press CTRL + X to stop the macro from running.
  Again, thanks for your query. We always welcome our readers to ask this type of info-ful questions.
  
  Regards,
  SHAHRIAR ABRAR RAFID
  Team ExcelDemy

How to Auto Populate Date in Excel When Cell Is Updated

1. Applying Excel VBA to Auto Populate Date When Cell Is Updated

2. Using VBA Macro to Auto Populate Date in Excel When Specific Cell Is Updated

Conclusion

Related Articles

Leave a reply Cancel reply