How do I track multiple changes to one cell?

T

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.
 
B

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
 
T

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.
 
B

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
 
T

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.
 
B

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
 
D

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.
 
Top