Updating a last modified date using the worksheet change event
 

I have an Excel 2003 workbook to manage large amounts of data. I want to
track changes made to the data, but I don't want to use Excel's track changes
feature as I don't like the display options.

I created two columns in my worksheet, Last Modified Date and Last Modified
Time. I want the values in these columns to be updated to the system date
and time, any time the value of any cell in the same row is changed.

The following code off the worksheet change event is close to what I want,
but when ranges of cells are pasted into my working area, I want ALL the last
modified date and times to be set for each row that was part of the paste.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Union(Target, Range("A8:O65536")).Address = "$A$8:$O$65536" Then
Cells(Target.Cells.Row, 20) = Date
Cells(Target.Cells.Row, 21) = Time
End If
Application.EnableEvents = True
End Sub

What do I use to get all the date/time values (columns 20 and 21) to change
for all the rows in my range?

--
Thanks,

Bruce Bolio

Updating a last modified date using the worksheet change event
 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell as Range
Application.EnableEvents = False
If Union(Target, Range("A8:O65536")).Address = "$A$8:$O$65536" Then
for each cell in Target
Cells(cell.Row, 20) = Date
Cells(cell.Row, 21) = Time
Next
End If

Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy

"Bruce Bolio" wrote:

I have an Excel 2003 workbook to manage large amounts of data. I want to
track changes made to the data, but I don't want to use Excel's track changes
feature as I don't like the display options.

I created two columns in my worksheet, Last Modified Date and Last Modified
Time. I want the values in these columns to be updated to the system date
and time, any time the value of any cell in the same row is changed.

The following code off the worksheet change event is close to what I want,
but when ranges of cells are pasted into my working area, I want ALL the last
modified date and times to be set for each row that was part of the paste.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Union(Target, Range("A8:O65536")).Address = "$A$8:$O$65536" Then
Cells(Target.Cells.Row, 20) = Date
Cells(Target.Cells.Row, 21) = Time
End If
Application.EnableEvents = True
End Sub

What do I use to get all the date/time values (columns 20 and 21) to change
for all the rows in my range?

--
Thanks,

Bruce Bolio