Excel 2000 - Derive Last Changed date for each row ?

[JohnBates]

Hi Excel experts -

For example, I have an Excel 2000 worksheet that has 3 columns: A1 A2 A3

Question: I want to add a 4th col to contain the date that
anything in that row gets changed. I've tried the simple
A4 =Now() function. Of course that is not what I need.

Is this possible to do... any hints please ?

Thank you. John

 

[SkipVought]

The solution is using VBA.

There is a Worksheet_Change for each worksheet object In that subroutine enter the following...

    With Target
        Cells(.Row, 4).Value = Now()
    End With

Remember to format Col "D" as Date/Time

Skip
metzgsk@voughtaircraft.com

 

[SkipVought]

John,
Here's some code to make you application a bit more general - ie not dependent on the date in Col "D".
The key thing for you to do is...
1. Lable the date column as ChangeDate (if you want different, then change the code to agree
2. be certain that your table has contiguous rows ie no row containing no data
3. start you table in column "A"
4. make sure that you header row containing headings is either in row 1 OR has a blank row above it.
The HeaderRow function figures out where your header row is under these rules

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rng As Range
    Set rng = Range(Cells(HeaderRow, 1), Cells(HeaderRow, 1).End(xlToRight))
    For Each cell In rng
        If cell.Value = "ChangeDate" Then iCol = cell.Column
    Next
    
    With Target
        If .Row > HeaderRow Then
            Cells(.Row, iCol).Value = Now()
        End If
    End With
End Sub

Function HeaderRow() As Long
    HeaderRow = Cells(Cells.Rows.Count, 1).End(xlUp).CurrentRegion.Row
End Function

Skip,
metzgsk@voughtaircraft.com

 

[JohnBates]

Skip - Thanks for taking the time to give me the solution.

Before I read your second post I added the little macro for Work_Change.....

This works perfectly.

Really appreciate it. John