Modification Time Update

[Kanmi]

Please i want to modify this formula to change when column content below it
change. "=TODAY()" or if other way to do it.

A B C
A1 Last Update 02/06/2009 03/07/2008
B2- 30% C2-40%
B3- 80% C3- 20%
B4- 45% C4- 90%

Assuming i change 40% on C2 Change to 100% today then 03/07/2008 should
automatically change to 06/24/2009. Please can anybody help me with this?
Appreciate your precious time. Thanks

 

[Bernie Deitrick]

Kanmi,

Right click the sheet tab, select "View Code", and paste this into the window that appears:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("2:4")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Cells(1, Target.Column).Value = Date
Application.EnableEvents = True
End Sub

This will change the date in the first row for any change in rows 2 to 4, if done one cell at a
time.

HTH,
Bernie
MS Excel MVP

 

[Kanmi]

Thanks but only working on one column i want to work on J10: S10 on heading
LAST UPDATE ROW. Please advice on what to do. Thanks Appreciate your time.

 

[Bernie Deitrick]

Kanmi,

It works on all columns, rows 2 to 4.

Change "2:4" to whatever rows you want it to work on "11:22" would work on rows 11 to 22 for
example.

And change the 1 in Cells(1, Target.Column) to the row number where you want the dates stored - for
example, for row 10:

Cells(10, Target.Column).Value = Date

If the label LAST UPDATE ROW can move around, then use


Private Sub Worksheet_Change(ByVal Target As Range)
Dim myR As Long
If Intersect(Target, Range("2:40")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
myR = Cells.Find("LAST UPDATE ROW").Row
Cells(myR, Target.Column).Value = Date
Application.EnableEvents = True
End Sub


HTH,
Bernie
MS Excel MVP