Compare two sheets with columns/rows inserted

[bcc5025]

Hi Everyone!

I've been working on this problem for a while and I still can't figure it out. Essentially I will be taking a snapshot of data, and then comparing it to new data months later to find out what changed. I need a VBA code to highlight these differences even when rows/columns are inserted.

[TABLE="width: 320"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD="colspan: 2"]Original Data:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Age[/TD]
[TD]Gender[/TD]
[TD]Height (in)[/TD]
[TD]Weight (lb)[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]21[/TD]
[TD]M[/TD]
[TD]72[/TD]
[TD]182[/TD]
[/TR]
[TR]
[TD]Sally[/TD]
[TD]23[/TD]
[TD]F[/TD]
[TD]69[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]35[/TD]
[TD]M[/TD]
[TD]70[/TD]
[TD]175[/TD]
[/TR]
[TR]
[TD]Rachel[/TD]
[TD]31[/TD]
[TD]F[/TD]
[TD]65[/TD]
[TD]140[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]27[/TD]
[TD]F[/TD]
[TD]66[/TD]
[TD]147[/TD]
[/TR]
[TR]
[TD]Greg[/TD]
[TD]27[/TD]
[TD]M[/TD]
[TD]69[/TD]
[TD]205[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 384"]
<colgroup><col span="6"></colgroup><tbody>[TR]
[TD="colspan: 4"]New Data (differences bolded):[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Age[/TD]
[TD]Gender[/TD]
[TD]Shoe Size[/TD]
[TD]Height (in)[/TD]
[TD]Weight (lb)[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]21[/TD]
[TD]M[/TD]
[TD]10[/TD]
[TD]72[/TD]
[TD]181[/TD]
[/TR]
[TR]
[TD]Sally[/TD]
[TD]23[/TD]
[TD]F[/TD]
[TD]7[/TD]
[TD]69[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]35[/TD]
[TD]M[/TD]
[TD]9.5[/TD]
[TD]70[/TD]
[TD]180[/TD]
[/TR]
[TR]
[TD]Brian[/TD]
[TD]29[/TD]
[TD]M[/TD]
[TD]9[/TD]
[TD]70[/TD]
[TD]186[/TD]
[/TR]
[TR]
[TD]Rachel[/TD]
[TD]31[/TD]
[TD]F[/TD]
[TD]6.5[/TD]
[TD]65[/TD]
[TD]140[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]28[/TD]
[TD]F[/TD]
[TD]8[/TD]
[TD]68[/TD]
[TD]147[/TD]
[/TR]
[TR]
[TD]Greg[/TD]
[TD]27[/TD]
[TD]M[/TD]
[TD]11[/TD]
[TD]69[/TD]
[TD]205[/TD]
[/TR]
</tbody>[/TABLE]

Anyone have suggestions? Index-match doesn't work because it won't recognize then rows/columns are inserted, so I guess I need some kind of two dimensional lookup? (Mary-Height was 66 now 68 = highlight)

Thanks!

 

[hiker95]

bcc5025,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

Here is a macro solution for you to consider.

The macro will first ask for the worksheet names (in this example, Original, and, New).

Both entered worksheet names will have to exist for the macro to continue.

Sample raw data in worksheet Original:

Excel 2007
	A	B	C	D	E	F
1	Name	Age	Gender	Height (in)	Weight (lb)
2	Jim	21	M	72	182
3	Sally	23	F	69	120
4	Bob	35	M	70	175
5	Rachel	31	F	65	140
6	Mary	27	F	66	147
7	Greg	27	M	69	205
8
Original

Sample raw data in worksheet New:

Excel 2007
	A	B	C	D	E	F	G
1	Name	Age	Gender	Shoe Size	Height (in)	Weight (lb)
2	Jim	21	M	10	72	181
3	Sally	23	F	7	69	120
4	Bob	35	M	9.5	70	180
5	Brian	29	M	9	70	186
6	Rachel	31	F	6.5	65	140
7	Mary	28	F	8	68	147
8	Greg	27	M	11	69	205
9
New

And, after the macro:

Excel 2007
	A	B	C	D	E	F	G
1	Name	Age	Gender	Shoe Size	Height (in)	Weight (lb)
2	Jim	21	M	10	72	181
3	Sally	23	F	7	69	120
4	Bob	35	M	9.5	70	180
5	Brian	29	M	9	70	186
6	Rachel	31	F	6.5	65	140
7	Mary	28	F	8	68	147
8	Greg	27	M	11	69	205
9
New

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub CompareNewToOriginal()
' hiker95, 12/20/2015, ME909209
Dim woname As String, wnname As String
Dim wo As Worksheet, wn As Worksheet
Dim lro As Long, lco As Long, lrn As Long, lcn As Long
Dim r As Long, c As Long
Dim t As Range, n As Range
woname = InputBox("Enter the Original worksheet name")
If Not WorksheetExists(woname) Then
  MsgBox ("Worksheet '" & woname & "' DOES NOT EXIST - macro termnated!")
  Exit Sub
End If
wnname = InputBox("Enter the New worksheet name")
If Not WorksheetExists(wnname) Then
  MsgBox ("Worksheet '" & wnname & "' DOES NOT EXIST - macro termnated!")
  Exit Sub
End If
Application.ScreenUpdating = False
Set wo = Sheets(woname)
Set wn = Sheets(wnname)
With wn
  .Activate
  lrn = .Cells(Rows.Count, 1).End(xlUp).Row
  lcn = .Cells(1, Columns.Count).End(xlToLeft).Column
  'Bold new Title Rows
  For c = 2 To lcn
    Set t = wo.Rows(1).Find(.Cells(1, c).Value, LookAt:=xlWhole)
    If t Is Nothing Then
      .Range(.Cells(1, c), .Cells(lrn, c)).Font.Bold = True
      .Columns(c).AutoFit
    End If
  Next c
  'Check Names
  For r = 2 To lrn
    Set n = wo.Columns(1).Find(.Cells(r, 1).Value, LookAt:=xlWhole)
    If n Is Nothing Then
      .Range(.Cells(r, 1), .Cells(r, lcn)).Font.Bold = True
    ElseIf Not n Is Nothing Then
      For c = 2 To lcn
        If Not .Cells(r, 1).Font.Bold = True Then
          Set t = wo.Rows(1).Find(.Cells(1, c).Value, LookAt:=xlWhole)
          If Not t Is Nothing Then
            If .Cells(r, c).Value <> wo.Cells(n.Row, t.Column) Then
              .Cells(r, c).Font.Bold = True
            End If
          End If
        End If
      Next c
    End If
  Next r
  .Columns(1).Resize(, lcn).AutoFit
End With
Application.ScreenUpdating = True
End Sub
Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the CompareNewToOriginal macro.