Vba - auto update date

[vba_rook]

Hi all,

This is my first time posting on here on this forum.

I am completely new to VBA and Macros but have done a bit of reading on forums across the web but am still struggling with figuring out all the VBA verbiage.

I am looking to write a code that will auto update the cell in column A with today's date whenever any cell in that row is entered or changed. The data table will have hundreds of rows of entries.

Much appreciation for the help in advance.

Thank you,
Sinny

 

[dermie_72]

try using:
=today()
in the cell. That should update automatically without the need of a macro

 

[vba_rook]

Hi dermie,

Thanks for the quick response.

By using the =today() function, the dates will auto-update even if I am just opening the excel file which is not what I am looking to do.

I only want the date value to change if another value in that row is changed or if new data is entered.

 

[dermie_72]

From what you're saying, the best option would be to have an event macro, that activates when a cell is changed.

See: How to run a macro when certain cells change in Excel
& Macro | Excel Macro | Run a Macro when Cell Value Changes

Both are excellent tools for learning how to do this if you're not sure how to yourself.

 

[L. Howard]

Hi vba_rook.

I did a "VBA DATE STAMP" search in help and found one written by Barb-B in 2010.

I did edit it a bit to suit your sheet and you may have to edit it some more to fit your range. (See 'my edit )

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim Fcells As Range
Dim CL As Range
Dim i As Integer ' my edit
i = Target.Column 'my edit

Application.ScreenUpdating = False
Application.EnableEvents = False

On Error Resume Next
'Set Fcells = Intersect(Target, Range("F:F"))
Set Fcells = Intersect(Target, Range("B1:K10")) 'my edit - adjust range to your hundreds of rows...
If Fcells <> Empty Then
For Each CL In Fcells.Cells
'CL.Offset(0, 2) = Date
CL.Offset(0, -(i - 1)) = Date 'my edit
Next CL
End If

Set Fcells = Nothing
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

HTH
Regards,
Howard