This code works great but it's not enough - I'm hoping this code can be modified to accommodate a larger range of content (A:W) doing the following:
Sheet4 is considered the "Original"
Sheet5 is the sheet that someone has made changes to
Sheet 6 is where the list of all found changes are posted (along with the cell refc of where the changed cell was found in Sheet5)
Compare Sheet 4's Column A (A2) to Sheet 5's Column A (A2)
Compare Sheet 4's Column B (B2) to Sheet 5's Column B (B2), etc. out through Column W (the format of both sheets will be identical)
It's one directional (no changes will ever be made to the "Original" Sheet4)
It's possible new rows will be added at the base of the evolving Sheet5 but no inserting of rows - so existing content should align nicely for comparisons.
Hopefully, the attached small sample file will help everyone see what the current code does:
(it is tied to Sheet1 being the Orig, Sheet2 being the changing sheet, Sheet3 is what the code found).
The new example of what I'm hoping someone knows how to achieve is found on Sheets 4, 5, & 6 (done manually)
My real file has approx. 20 columns of a lot of categorical data so listing everything going downward on Sheet 6 was much easier to read..
Having a cell refc listed makes it much easier to scan the findings and determine whether or not it's okay to retain the change made (and where to locate it quickly if not)
Thanks greatly!
Sub ListChanges()
'Compares Sh1's Orig Content to Sh2's Changed Content
'Changes found are listed on Sh3 (showing the before and after state of each cell)
'This only deals with 2 columns and works sideways to the right
'
'Sheets 4, 5 and 6 demonstrate what is needed - hoping the below can be modified to:
'Need it to accomodate a large # of columns (A:W)
'And need it to list findings going down as shown in Sheet 6
'To include cell reference of where the changed cell was
'
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, lr1 As Long, lr2 As Long, rng1 As Range, rng2 As Range, c As Range
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
Set sh3 = Sheets(3) 'Edit sheet name
lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row 'Get the last row with data for both list sheets
lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row
Set rng1 = sh1.Range("A2:A" & lr1) 'Establish the ranges on both sheets
Set rng2 = sh2.Range("A2:A" & lr2)
With sh3 'If header not there, put them in
If .Range("A1") = "" And .Range("B1") = "" Then
.Range("A1") = "Original Value"
.Range("B1") = "Changed Value"
End If
End With For Each c In rng1 'Run a loop for each list ID mismatches and paste to sheet 3.
If WorksheetFunction.CountIf(rng2, c.Value) = 0 Then
sh3.Cells(Rows.Count, 1).End(xlUp)(2) = c.Value
End If
Next
For Each c In rng2
If Application.CountIf(rng1, c.Value) = 0 Then
sh3.Cells(Rows.Count, 2).End(xlUp)(2) = c.Value
End If
Next
End Sub