Auto update of date

georgecheng

Board Regular
Joined
Feb 27, 2008
Messages
57
I'm looking for a way that when entering data in an cell in column A the current date to automatically appear in column T. I want the date to be a fixed.
After saving the sheet, the next time I open it the dates/times will still have to be the same as they were the first time.
Can anyone help me with this?
Thanks
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
HI

Right-click on the tab name in the main Excel window and select View COde. Paste the following into the code module that will open up. Back in the sheet, every time you enter a value in col A, the corresponding cell in col T will be updated with today's date. If you delete a value in col A, col T will also be cleared.


Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, cell As Range
Set r = Intersect(Range("A:A"), Target)
If Not r Is Nothing Then
    Application.EnableEvents = False
    For Each cell In r
        If Not IsEmpty(cell.Value) Then
            With Cells(cell.Row, "T")
                .Value = Date
                .NumberFormat = "dd mmm yyyy"
            End With
        Else
            Cells(cell.Row, "T").ClearContents
        End If
    Next cell
    Application.EnableEvents = True
End If
End Sub
 
Upvote 0
This goes in the worksheet module
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
 If Target.Cells.Count > 1 Then Exit Sub
If Target <> "" Then
Target.Offset(0, 19).Value = Date
End If
End Sub
 
Upvote 0
Thanks RichardSchollar, it works. Is there anyway to do this without using code, but using formula etc?

BTW, simon, what do you mean in the worksheet module. This is the first time I using code under excel.
 
Upvote 0
There's no way to keep the date 'fixed' by using formulas (AFAIK).

By worksheet module Simon was referring to the same place that you posted my code to :)
 
Upvote 0
Can avoid looping each cell ;-

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
Set r = Intersect(Range("A:A"), Target)
If Not r Is Nothing Then
    Application.EnableEvents = False
    r.Offset(, 19) = Date
    On Error Resume Next
    r.SpecialCells(xlCellTypeBlanks).Offset(, 19).ClearContents
    On Error GoTo 0
    Application.EnableEvents = True
End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,215,981
Messages
6,128,080
Members
449,418
Latest member
arm56

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top