Compare Excel sheets

Hi Pedro,

Here's a macro (actually two macros, one to do the compare, the other to undo the highlighting) that compares two sheets. Simply select the two sheets you want to compare, with the sheet you want the hilited differences to show on to be the active sheet. Then run the CompareSheets macro. This comparison is a contents-only compare, i.e., not designed to compare cell formats or formulas, but could be easily modified to do these instead (or in addition).

After you have done the compare, just enter Ctrl-u to undo the highlighting.

Type CellDiff
PreviousColorIndex As Integer
Address As String
End Type

Dim SaveDiff() As CellDiff
Dim ASh As Worksheet 'The active worksheet

Sub CompareSheets()
'This macro compares the contents of all cells on two selected worksheets
'(ignoring other types of sheets), and highlights the cells that are different
'on the active worksheet (the worksheet that is on top)

Dim CSh As Worksheet 'The comparison worksheet
Dim Cel As Range
Dim nDifs As Long 'The number of differences found

If ActiveWindow.SelectedSheets.Count <> 2 Then
MsgBox "Two worksheets must be selected", vbExclamation, "Compare Sheets Error"
Exit Sub
End If

If ActiveWindow.SelectedSheets(1) Is ActiveSheet Then
Set ASh = ActiveWindow.SelectedSheets(1)
Set CSh = ActiveWindow.SelectedSheets(2)
Else
Set ASh = ActiveWindow.SelectedSheets(2)
Set CSh = ActiveWindow.SelectedSheets(1)
End If

nDifs = 0

For Each Cel In Union(ASh.UsedRange, ASh.Range(CSh.UsedRange.Address))
If Cel.Value <> CSh.Cells(Cel.Row, Cel.Column) Then
nDifs = nDifs + 1
ReDim Preserve SaveDiff(nDifs)
SaveDiff(nDifs).Address = Cel.Address
SaveDiff(nDifs).PreviousColorIndex = Cel.Interior.ColorIndex
Cel.Interior.ColorIndex = 6
End If
Next Cel

MsgBox nDifs & " differences found." & vbCrLf & _
"Type Ctrl-u to undo highlighting", _
vbInformation, "Compare Sheets Results"

Application.OnKey "^u", "UndoDifHilites"

End Sub

Sub UndoDifHilites()

Dim iCell As Long

For iCell = 1 To UBound(SaveDiff)
ASh.Range(SaveDiff(iCell).Address).Interior.ColorIndex = SaveDiff(iCell).PreviousColorIndex
Next iCell

'release OnKey definition
Application.OnKey "^u"

End Sub

Happy comparing.

Damon