Modification Time Update
 

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

Modification Time Update
 

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" wrote in message
...
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

Modification Time Update
 

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" wrote:

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" wrote in message
...
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

Modification Time Update
 

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


"Kanmi" wrote in message
...
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" wrote:

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" wrote in message
...
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