change automatically a value when another cell value changes
I keep a list of students and activities. Because the number of students per
activity is limited, I would to track the date and time when they selected their activity: Student Activity Date --------- -------- ----------- Smith Tennis 02/01/05 I would like the cells in the Date column to change automatically when the Activity value is changed. Thanks a lot, |
CC, here is one way using Worksheet_Change event
Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'will put date & time in column C when something is put in B If Target.Count 1 Then Exit Sub If Target.Column = 2 Then Target.Offset(0, 1).Value = Now() 'change Now() to Date if you want the date only End If End Sub To put in this macro right click on the worksheet tab and view code, in the window that opens paste this code, press Alt and Q to close this window and go back to your workbook. If you are using excel 2000 or newer you may have to change the macro security settings to get the macro to run. To change the security settings go to tools, macro, security, security level and set it to medium "CC" wrote in message ... I keep a list of students and activities. Because the number of students per activity is limited, I would to track the date and time when they selected their activity: Student Activity Date --------- -------- ----------- Smith Tennis 02/01/05 I would like the cells in the Date column to change automatically when the Activity value is changed. Thanks a lot, |
Hi CC
You can do it with the change event of the worksheet This example will place the date/time in the B column if you change a cell in the range A1:A20. Place the code in the Sheet module Right click on a sheet tab and choose view code Paste the code there Alt-Q to go back to Excel Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("a1:a20"), Target) Is Nothing Then Target.Offset(0, 1).Value = Format(Now, "mm-dd-yy hh:mm:ss") End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "CC" wrote in message ... I keep a list of students and activities. Because the number of students per activity is limited, I would to track the date and time when they selected their activity: Student Activity Date --------- -------- ----------- Smith Tennis 02/01/05 I would like the cells in the Date column to change automatically when the Activity value is changed. Thanks a lot, |
All times are GMT +1. The time now is 12:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com