Re: How can I track changes to a cell in Excel?

Giganews Newsgroups
Subject: Re: How can I track changes to a cell in Excel?
Posted by:  Ardus Petus (…
Date: Mon, 22 May 2006

Here is one solution:

You'll have to change the constants in the event proc to suit your needs.

Following code should be placed in worksheet's code:


Private Sub Worksheet_Change(ByVal Target As Range)
    'Column to be watched
    Const sWatch As String = "F"
    'Column of reference data that will show on Track sheet
    Const sRef As String = "A"

    Dim rWatch As Range
    Dim rCell As Range
    Dim sUser As String
    Dim lOffset As Long

    Set rWatch = Intersect(Target, Columns(sWatch))
    If rWatch Is Nothing Then Exit Sub
    sUser = Environ("username")
    lOffset = Columns(sRef).Column - Columns(sWatch).Column
    With Worksheets("Track")
        For Each rCell In rWatch
            With .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                .Value = rCell.Offset(0, lOffset)
                .Offset(0, 1).Value = Now
                .Offset(0, 2).Value = sUser
                .Offset(0, 3).Value = rCell.Value
            End With
        Next rCell
    End With
End Sub

"Ukyankee" <Ukyank…> a écrit dans le message de
news: 37AA8F2B-9B75-4C41-B124-FCB8D58D1B…
>I have a spreadsheet used to track actions by numerous folks.  There is a
> column for a due date that I would like to see when and how often it is
> changed.  I looked at Track Changes, but it tracks ALL changes in all
> cells
> (adds, edits etc) - which is too much info.  I would like to see only the
> changes on a particular column after the initial entry.  Any thoughts
> would
> be greatly appreciated.


In response to

How can I track changes to a cell in Excel? posted by Ukyankee on Mon, 22 May 2006