Looking for changes in cells - Excel files

I have 2 excel files with a lot of data in each. The data is structured exactly the same in both files but the values might have changed as the data is from two different times.

Basically I want to find some way to automatically compare values in each cell for the two files and highlight the cells that have开发者_StackOverflow中文版 changed values in file #2.

Kindly share your ideas!

Example:

File 1 :

a / 1 / 2

File 2 :

a / 1 / 8

(/ - indicates new cell)

This may not be the most efficient way (can handle 25k cells in a few seconds, though), but it more than makes up for it in simplicity.

This will look at every cell in Sheet2 and compare it against the value in the cell at the same address in Sheet1 of the file you specify. If it's different, the cell in Sheet2 is highlighted yellow.

Sub FindDifferences()

Application.ScreenUpdating = False
Dim cell As Range
Dim wkb1 As Workbook
Dim wks1 As Worksheet

Set wkb1 = Workbooks.Open(Filename:="C:\MyBook.xls")
Set wks1 = wkb1.Worksheets("Sheet1")

For Each cell In ThisWorkbook.Sheets("Sheet2").UsedRange
    If cell.Value <> wks1.Cells(cell.Row, cell.Column).Value Then
        cell.Interior.Color = vbYellow
    End If
Next

wkb1.Close
Application.ScreenUpdating = True
End Sub

Note: You could easily tailor this to compare 2 sheets in the same file by simple removing the wkb1 and wks1 variables and changing wks1.Cells... to Sheets("Sheet1").Cells...

You can use this online website - xlcomparator.net (click on the flag on the top right for an english version).

Or try this software: http://www.formulasoft.com/excel-compare.html

Or try this kind of macro (that check the first column) and adapt it to your needs:

sub compare()
   Application.ScreenUpdating = False
   Dim coll1 As New Collection, coll2 As New Collection
   Dim cell1 As Range, cell2 As Range
   Dim Element1 As Object, Element2 As Object

 Workbooks("workbook1.xls").Activate
   For Each Cellule1 In Range("a:a")

  coll1.Add cell1
   Next Cellule1

 Workbooks("workbook2.xls").Activate
   For Each cell2 In Range("a:a")

  coll2.Add cell2
   Next cell2
   For Each Element1 In coll1
     For Each Element2 In coll2
      If Element1 <> Element2 Then
         Element1.Font.Color = vbRed
  Else
         Element1.Font.Color = vbBlack
         Exit For
      End If
     Next Element2
   Next Element1

 Application.ScreenUpdating = True
 end sub

Source - excelabo, a french website

Two further options:

1. Spreadsheet Advantage, http://www.spreadsheetadvantage.com/, where you can get a free 30 day trial

This is my favourite tool as it also offers a row and column alignment option to ensure both sheets are presented indentically by row and column, before running the compare outputs code to highlight any differences

2. Myrna Lawson's compare.xla addin (free) available at Chip Pearson's site http://www.cpearson.com/Zips/Compare.zip