Posted to microsoft.public.excel.programming
|
|
I need a function that will trap & manage a change in cell val
Thank you, Tom. This response will be greatfully used and acknowledged.
What a response!
"Tom Ogilvy" wrote:
Right click on the sheet tab and select View Code.
in the resulting module, paste in code like this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Count 1 Then Exit Sub
On Error GoTo ErrHandler
' use your list of cells to react to
Set rng = Range("B1,C11,D12,M21,A5:A30,C1:C5")
If Not Intersect(Target, rng) Is Nothing Then
Application.EnableEvents = False
Target.Offset(0, 1).Value = Now
Target.Offset(0, 1).NumberFormat = "mm/dd/yyyy hh:mm"
Target.Offset(0, 1).EntireColumn.AutoFit
End If
ErrHandler:
Application.EnableEvents = True
End Sub
this uses the worksheet Change event. If you are not familiar with events,
see Chip Pearson's site
http://www.cpearson.com/excel/events.htm
--
Regards,
Tom Ogilvy
"Terry" wrote:
Scenario
A value exists in a cell
The value in the cell is changed by data entry
A date would be populated in an adjacent cell reflecting the date of change
- for that cell only
My worksheet has numerous places that this function would need to work
|