Hi Miri,
There are a number of programs out in the market that provide more complex
comparision.
Otherwise you could simply make a new sheet and copy the following formula
into A1:
=if('[book1.xls]sheet1'!a1='[book2.xls]sheet1'!a1,"","<<<")
anotherway to do it is to run the following you need to set up the workbook
and worksheet names and it uses sheet1 of the workbook where the macro is to
display the results.
Option Explicit
Sub checksheets()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim lRowMax As Long
Dim lColumnMax As Long
Dim lRow As Long
Dim lColumn As Long
Dim lCurrentRow As Long
Dim lCellRowsMax As Long
Application.ScreenUpdating = False
' change as required
Set ws1 = Workbooks("book3").Worksheets("sheet1")
Set ws2 = Workbooks("book2").Worksheets("sheet1")
Set ws3 = ThisWorkbook.Worksheets(1)
ws3.Cells.Clear
ws3.Range("A1") = "File 1:"
ws3.Range("A2") = "File 2:"
ws3.Range("B1") = ws1.Parent.Name & "/" & ws1.Name
ws3.Range("B2") = ws2.Parent.Name & "/" & ws2.Name
ws3.Range("A5") = "Cell"
ws3.Range("B4:C4") = "Value"
ws3.Range("D4:E4") = "Formula"
ws3.Range("B5") = "File 1"
ws3.Range("C5") = "File 2"
ws3.Range("D5") = "File 1"
ws3.Range("E5") = "File 2"
lCurrentRow = 5
lColumnMax = Application.WorksheetFunction.Max( _
ws1.UsedRange.Column + ws1.UsedRange.Columns.Count - 1, _
ws2.UsedRange.Column + ws2.UsedRange.Columns.Count - 1)
lRowMax = Application.WorksheetFunction.Max( _
ws1.UsedRange.Row + ws1.UsedRange.Rows.Count - 1, _
ws2.UsedRange.Row + ws2.UsedRange.Rows.Count - 1)
lCellRowsMax = ws1.Cells.Rows.Count
For lColumn = 1 To lColumnMax
For lRow = 1 To lRowMax
If ws1.Cells(lRow, lColumn) <> _
ws2.Cells(lRow, lColumn) Or _
ws1.Cells(lRow, lColumn).Formula <> _
ws2.Cells(lRow, lColumn).Formula Then
lCurrentRow = lCurrentRow + 1
If lCurrentRow > lCellRowsMax Then
MsgBox "Run out of space....", vbOKOnly
ws3.Range("A3") = "Run out of space"
Application.ScreenUpdating = True
Exit Sub
End If
ws3.Cells(lCurrentRow, 1) = _
ws1.Cells(lRow, lColumn).Address
ws3.Cells(lCurrentRow, 2) = _
ws1.Cells(lRow, lColumn).Value
ws3.Cells(lCurrentRow, 3) = _
ws2.Cells(lRow, lColumn).Value
ws3.Cells(lCurrentRow, 4).Value = _
"'" & ws1.Cells(lRow, lColumn).Formula
ws3.Cells(lCurrentRow, 5).Value = _
"'" & ws2.Cells(lRow, lColumn).Formula
End If
Next lRow
Next lColumn
Application.ScreenUpdating = True
End Sub
