Comparing Workbooks

How to Compare 2 workbooks ?

This procedure creates a new workbook which lists the comparison results for each worksheet in the workbooks.
Open the 2 workbooks you would like to compare.
In this examples the files have the names "BetterSolutions_1.xlsm" and "BetterSolutions_2.xlsm".

Public Sub CompareTwoWorkbooks() 
  Dim WS As Worksheet 
  Workbooks.Add 
  For Each WS In Workbooks("BetterSolutions_1.xlsm").Worksheets 
    Call CompareWorksheets(WS, Workbooks("BetterSolutions_2.xlsm").Worksheets(WS.Name)) 
  Next 
End Sub 

Public Sub CompareWorksheets(ByVal WS1 As Worksheet, _ 
                             ByVal WS2 As Worksheet) 
Dim iRow As Integer 
Dim iCol As Integer 
Dim R1 As Range 
Dim R2 As Range 

  ' add the corresponding worksheet for the results
  Worksheets.Add.Name = WS1.Name         
  Range("A1:D1").Value = Array("Address", "Difference", WS1.Parent.Name, WS2.Parent.Name) 
  Range("A2").Select 
  For iRow = 1 To Application.Max(WS1.Range("A1").SpecialCells(xlLastCell).Row, _ 
                      WS2.Range("A1").SpecialCells(xlLastCell).Row) 
    For iCol = 1 To Application.Max(WS1.Range("A1").SpecialCells(xlLastCell).Column, _ 
                      WS2.Range("A1").SpecialCells(xlLastCell).Column) 
      
      Set R1 = WS1.Cells(iRow, iCol) 
      Set R2 = WS2.Cells(iRow, iCol) 
      
      ' compare the types to avoid getting VBA type mismatch errors.
      If (TypeName(R1.Value) <> TypeName(R2.Value)) Then 
        Call LogDifference(R1.Address, "Type", R1.Value, R2.Value) 
      ElseIf (R1.Value <> R2.Value) Then 
        If (TypeName(R1.Value) = "Double") Then 
          If (Abs(R1.Value - R2.Value) > R1.Value * 10 ^ (-12)) Then 
            Call LogDifference(R1.Address, "Double", R1.Value, R2.Value) 
          End If 
        Else 
            Call LogDifference(R1.Address, "Value", R1.Value, R2.Value) 
        End If 
      End If 
      
      ' record formulae without leading "=" to avoid them being evaluated
      If (R1.HasFormula = True) Then 
        If (R2.HasFormula = True) Then 
          If (R1.Formula <> R2.Formula) Then 
            Call LogDifference(R1.Address, "Formula", Mid(R1.Formula, 2), Mid(R2.Formula, 2)) 
          End If 
        Else 
          Call LogDifference(R1.Address, "Formula", Mid(R1.Formula, 2), "**no formula**") 
        End If 
      Else 
        If (R2.HasFormula = True) Then 
          Call LogDifference(R1.Address, "Formula", "**no formula**", Mid(R2.Formula, 2)) 
        End If 
      End If 
      If (R1.NumberFormat <> R2.NumberFormat) Then 
        Call LogDifference(R1.Address, "NumberFormat", R1.NumberFormat, R2.NumberFormat) 
      End If 
    Next iCol 
  Next iRow 
  With ActiveSheet.UsedRange.Columns 
    .AutoFit 
    .HorizontalAlignment = xlLeft 
  End With 
End Sub 

Public Sub LogDifference(ByVal Address As String, _ 
                         ByVal What As String, _ 
                         ByVal V1 As Variant, _ 
                         ByVal V2 As Variant) 
  ActiveCell.Resize(1, 4).Value = Array(Address, What, V1, V2) 
  ActiveCell.Offset(1, 0).Select 
  If ActiveCell.Row = Rows.Count Then 
    Call MsgBox("Too many differences", vbExclamation) 
    End 
  End If 
End Sub