I need a function that will trap & manage a change in cell value
 

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

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

I need a function that will trap & manage a change in cell val
 

How would this code be changed to update the change date to the comment of
the same cell being changed rather than an adjacent cell?

"Terry" wrote:

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

I need a function that will trap & manage a change in cell val
 

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.NoteText Text:=Format(Now, "mm/dd/yyyy hh:mm")
End If
ErrHandler:
Application.EnableEvents = True

End Sub

--
Regards,
Tom Ogilvy


"Terry" wrote:

How would this code be changed to update the change date to the comment of
the same cell being changed rather than an adjacent cell?

"Terry" wrote:

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