How do I track multiple changes to one cell?

[Tony B]

I want to be able to see a history of all the changes made to a specific
cell. Right now, I'm only able to see the latest change. It would be very
beneficial to me if I could see the full history of changes to one cell.
Thanks in advance for you help.

[Bernie Deitrick]

Tony,

The code below will keep track of the changes to cell A1, listing all the
values and the date/time changed. The list will start at the bottom of
column A -which will have the values, and column B will have the dates - and
progress down as changes continue.

Copy the code, right-click the sheet tab, select "View Code" and paste the
code in the window that appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address <> "$A$1" Then Exit Sub
Application.EnableEvents = False
Range("A65536").End(xlUp)(2).Value = Range("A1").Value
Range("A65536").End(xlUp)(1, 2).Value = Now()
Application.EnableEvents = True
End Sub


"Tony B" <Tony [email protected]> wrote in message
news:[email protected]...
> I want to be able to see a history of all the changes made to a specific
> cell. Right now, I'm only able to see the latest change. It would be very
> beneficial to me if I could see the full history of changes to one cell.
> Thanks in advance for you help.

[Tony B]

When I do that and try to enter (change) a value for cell A1, I get a syntax
error.

Also, I'm going to be needing to track changes for many cells within a
worksheet.

[Bernie Deitrick]

Tony,

The code worked for me: check for extra line breaks (though I can't imagine
why they would be there). Also, what is the line that throws the error?

Which cells do you want to track? Would a separate sheet with the changes
entered in a database be OK? You could filter based on the cell address
(which would be part of the database) to see the changes made to specific
cells.

HTH,
Bernie
MS Excel MVP


"Tony B" <Tony [email protected]> wrote in message
news:[email protected]...
> When I do that and try to enter (change) a value for cell A1, I get a
syntax
> error.
>
> Also, I'm going to be needing to track changes for many cells within a
> worksheet.
>

[Tony B]

I'm getting the error on the 2nd line
If Target.Cells.Count > 1 Then Exit Sub

This worksheet is going to be fairly large. I would like to be able to see
the history on screen as I move my cursor over a given cell.

Thanks again for helping me through this.

[Bernie Deitrick]

Tony,

I have no idea why that should error. Send me a gutted version of a
workbook that throws that error, and I will take a look.

When replying, take out the spaces and change dot to .

HTH,
Bernie
MS Excel MVP


"Tony B" <[email protected]> wrote in message
news:[email protected]...
> I'm getting the error on the 2nd line
> If Target.Cells.Count > 1 Then Exit Sub
>
> This worksheet is going to be fairly large. I would like to be able to see
> the history on screen as I move my cursor over a given cell.
>
> Thanks again for helping me through this.

[Dave Peterson]

Just a guess...

Sometimes copying from the web page can introduce weird HTML characters into
your text.

I'd try deleting that line and retyping it.

Tony B wrote:
>
> I'm getting the error on the 2nd line
> If Target.Cells.Count > 1 Then Exit Sub
>
> This worksheet is going to be fairly large. I would like to be able to see
> the history on screen as I move my cursor over a given cell.
>
> Thanks again for helping me through this.

--

Dave Peterson

[Piranha]

Tony,

The code below will keep track of the changes to cell A1, listing all the
values and the date/time changed. The list will start at the bottom of
column A -which will have the values, and column B will have the dates - and
progress down as changes continue.

Copy the code, right-click the sheet tab, select "View Code" and paste the
code in the window that appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address <> "$A$1" Then Exit Sub
Application.EnableEvents = False
Range("A65536").End(xlUp)(2).Value = Range("A1").Value
Range("A65536").End(xlUp)(1, 2).Value = Now()
Application.EnableEvents = True
End Sub


"Tony B" <Tony [email protected]> wrote in message
news:[email protected]...
> I want to be able to see a history of all the changes made to a specific
> cell. Right now, I'm only able to see the latest change. It would be very
> beneficial to me if I could see the full history of changes to one cell.
> Thanks in advance for you help.

This code works for me. Can the target address be changed
so that the log will be on a different sheet? (IE: Sheet2)
I tried it but had no luck.
Thx
Dave

[Robert Hayes]

This code was helpful - I've got it working (in 2010) but am actually trying to achieve the same result at the end of a row as opposed to column (i.e., change captured in last active column in a row, date captured to the right of that in same row). Simply changing xlUp to xlLeft isn't doing the trick. Any suggestions? Also, I'm unsure about the (1) and the (1, 2) after the Up statements.

Any help would be appreciated.