If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 news 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 |
#3
|
|||
|
|||
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 news 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 |
#4
|
|||
|
|||
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 news 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 |
Thread Tools | |
Display Modes | |
|
|