Problem updating audit trail

[gtol]

Hi,

I am new to the forum and I am looking for some help please.

I am building an application to manage project resources in Excel 2003 using VBA. I will be sharing the sheet with managers and allowing them to make changes but I want to have an audit trail of what they have changed.

On one sheet I have a table of projects consisting of 10 columns and n rows. I have a 'Projects' range defined on the 1st column and a 'ProjAuditInfo' range defined on the 10th column.

I have been able to get the auditing to work up to a point using the worksheet change event with an offset from the target cell of (0, 9) which updates Column 10. See below for the code I have been using :

Private Sub Worksheet_Change(ByVal Target As Range)

 'update the audit log if there is a change to any single cell in the Projects range
   With Target
     If .count > 1 Then Exit Sub
        If Not Intersect(Range("Projects"), .Cells) Is Nothing Then
            Application.EnableEvents = False
          
            With .Offset(0, 9)
                 .Value = Application.UserName & " " & Format(Date, "dd/mm/yy") & " " & Format(Time, "hh:mm")
            End With
          
            Application.EnableEvents = True
        End If
    End With

End Sub

This works fine to track if any single cells have been changed but only in the Projects range (Col 1).

My problem though is that I want to extend the target range to include additional contiguous columns in the table (not just column 1) but I still want to have the audit log updated in col 10 (ProjAuditInfo).

I have tried several things to try to get this to work and I haven't been able to find an answer to this problem in any of the forums. All of the worksheet change solutions seem to assume that a single column is being checked.

So I need some help to come up with the code that will take the target cell from anywhere in the expanded range (let's call this "ExpProjects") and go over to the same row on the "ProjAuditInfo" range and update this cell only with the relevant audit info.

I would be very grateful for any help with this problem,

Many thanks,


gtol