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

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