How can I change the date automatically when a row has been updated 1

[Aspesim]

Thanks Skip


Hi Guys

To say I'm newbie at this is an understatement.

I need to know how to add the date (say in coloum L)in MS EXCEL on which the row (A - K) had been last modified.

I've read some threads on this but I don't understand them.

First of all where would you enter the code?

I'll learn one step at a time I guess so please be patient with me as I'll be back time and again until I get it right

Kindest Regards
Aspesim

 

[SkipVought]

Hi,

You must use a Worksheet Event: the Worksheet_Change event.

Right click your sheet tab and select View Code

Past this code into the code window.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim t As Range
    For Each t In Target
        Intersect(t.EntireRow, Cells(1, "L").EntireColumn).Value = Now
    Next
End Sub

change a value on your sheet and observe.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Aspesim]

Thanks Skip it worked just great.

However: What do I enter if I want column A and all columns after L to be exempt from the automatic date entry?

Kindest Regards
Aspesim

 

[SkipVought]

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim t As Range
    For Each t In Target
        Intersect(t.EntireRow, [b]Range("A:K"),[/b] Cells(1, "L").EntireColumn).Value = Now
    Next
End Sub

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

...oops

make that Range("B:L")

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Aspesim]

Hi Skip

Thanks for your replies. However when i make a change in coloumn A it stills updates the (Now)/ date cell although I made the Range("B:L"). Also any changes to cells after coloumn L updates the (Now) / date cell?

Aspesim

 

[PHV]

For Each t In Target
    If Not (Intersect(t, Range("B:L")) Is Nothing) Then
        Intersect(t.EntireRow, Range("A:K"), Cells(1, "L").EntireColumn).Value = Now
    End If
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Sorry, I went off hald cocked!

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim t As Range
    For Each t In Target
        If Not Intersect(t, Range("B:K")) Is Nothing Then
            Intersect(t.EntireRow, Cells(1, "L").EntireColumn).Value = Now
        End If
    Next
End Sub

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}