VBA Code to Track Changes in Excel Sheet

[corey39]

I have the attached file and I am looking into creating VBA code that will track changes that are made to the sheet. I would like the changes to be housed in two separate tabs. I would like changes that are made to the cells highlighted in green to show up in the Approval Tracker tab and the cells that are highlighted in yellow to show up in the Change Tracker tab. For the cells highlighted in green all I need is the user name, cell reference, date and time of the change and the data that shows in the cells. For the yellow highlighted portion in the attached file I need the user name, cell reference, the old value, the new value, the old formula, the new formula and the date and time. I am not sure if this is possible. Thank you for any assistance that you can provide for this project.

[SamT]

I don't have Office on this computer, so this is a generic response

Sub Worksheet_Change(byVal Target as Range)
Dim GCells as Range: Set GCelss = Range(???)
Dim yCells as Range: Set YCells = Range(???)

   If Not Intersect(Target, Gcells) is Nothing Then LogGCells Intersect(Target, Gcells)
   If Not Intersect(Target, YCells) is Nothing Then LogYCells Intersect(Target, YCells)
End Sub

Sub LogGCells(ByVal Target as Range
Dim Cel As Range
For each Cel in Target
   With Sheets("Approval Tracker").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
     .Value = Environ("UserName")
     .Offset(, 1) = Cel.Address
     .Offset(, 2) = Now
     .Offset(, 3) = Cel.Value
  End With 
Next Cel
End sub

Repeat for Sub LogYCells, changing the Sheet name assignment to suit.


This obviously won't work i you are using Conditional Formatting to highlight cells. In that case, Good Luck and Have Fun assigning Ranges to GCells and YCells.