Insert date when data is modified

[Guest]

I have rows of data that I will periodically update. What I want to do is
have a cell at the end of the row that shows the date when data in just that
row was last updated.
For example, the rows might look like this:
13 42 23 45 90 ModifyDateRow1
52 32 97 74 37 ModifyDateRow2

I assumed I could do it with VBA, but when I tried to write it, I found that
I wasn't sure how to proceed. The code I tried is below, but the TODAY
function doesn't work ("Object doesn't support this property or method.").

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, Range("A1:E20")) Is Nothing) Then
With WS.Cells(Target.Row, 6)
.Value = Application.WorksheetFunction.Today()
.NumberFormat = "mm/dd/yyyy"
End With
Exit Sub
End If
End Sub

 

[Niek Otten]

Look here:

http://www.mcgimpsey.com/excel/timestamp.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have rows of data that I will periodically update. What I want to do is
| have a cell at the end of the row that shows the date when data in just that
| row was last updated.
| For example, the rows might look like this:
| 13 42 23 45 90 ModifyDateRow1
| 52 32 97 74 37 ModifyDateRow2
|
| I assumed I could do it with VBA, but when I tried to write it, I found that
| I wasn't sure how to proceed. The code I tried is below, but the TODAY
| function doesn't work ("Object doesn't support this property or method.").
|
| Option Explicit
| Private Sub Worksheet_Change(ByVal Target As Range)
| Dim WS As Worksheet
| Set WS = Worksheets("Sheet1")
| If Not (Application.Intersect(Target, Range("A1:E20")) Is Nothing) Then
| With WS.Cells(Target.Row, 6)
| .Value = Application.WorksheetFunction.Today()
| .NumberFormat = "mm/dd/yyyy"
| End With
| Exit Sub
| End If
| End Sub
|

 

[Gord Dibben]

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, Range("A1:E20")) Is Nothing) Then
With WS.Cells(Target.Row, 6)
.Value = Format(Date, "mm/dd/yyyy")

End With
Exit Sub
End If
End Sub


Gord Dibben MS Excel MVP

 

[Guest]

Thanks. That worked well.
~ Horatio

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, Range("A1:E20")) Is Nothing) Then
With WS.Cells(Target.Row, 6)
.Value = Format(Date, "mm/dd/yyyy")

End With
Exit Sub
End If
End Sub


Gord Dibben MS Excel MVP