ExcelBanter - Identify Cell by Column Header and ID in Col A (Track Changes)

You may need to build your own archive history file. It sounds like the
changes you want to track are specific to a particular worksheet - if so you
can use the Worksheet_Change() event procedure in that worksheet's module,
otherwise you can use Workbook_SheetChange() in the ThisWorkbook module.
Using code, getting the column header and the ID from column A would be easy;
you can also get the new value if you want to record that; but the old value
is a little trickier to get. You could perhaps use the SelectionChange event
procedure to record the value in the cell when the user moves into it and
store this in a module-level variable. Then, after capturing the info, use
Open and Write# to append it to your archive file. Here is the basic
skeleton for what I am suggesting (implemented for one sheet only, and
assuming the user will only change one cell at a time - i.e. no copy/paste to
multiple cells) - perhaps you can use this code or modify it as needed:

In the Worksheet module:

Private OldValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)

Open "C:\Mylogfile.txt" For Append As #1
Write #1, Target.Address, _
Target.EntireColumn.Cells(1, 1).Value, _
Target.EntireRow.Cells(1, 1).Value, _
OldValue, Target.Value
' Appends to archive the cell address at time of change,
' the column header, row ID,
' value before change, changed value
Close #1

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

OldValue = Target.Value

End Sub

"Bettergains" wrote:

Hello:
I need to archive change history. When using "Track Changes", the change is
tracked per cell / target. However, the xls file is updated daily and the
range is no longer applicable as new rows are added each day. I therefore
need to add a module that will look at the range (on another sheet), offset
by the row# up to the column header, grab the header name, then offset to the
left to Col A to grab the ID (value in RxC1). I'm SURE someone already has
this worked out. Would you be willing to share?

Thanks much, Bettergains