Looking for changes in cells - Excel files

Question

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 changed values in file #2.

Kindly share your ideas!

Example:

File 1 :

a / 1 / 2

File 2 :

a / 1 / 8

(/ - indicates new cell)

Is it only values you want to compare? Not formats, font size, color etc.? Also, is all your data in one sheet or on multiple sheets and what is "a lot of data in each"? — Alex P, Oct 05 '11 at 18:04
Related question: http://stackoverflow.com/questions/114698/how-do-i-diff-two-spreadsheets — barrowc, Oct 06 '11 at 23:02
only want to compare values but would like the output to highlight the values that are different. the two data sets I am comparing are in 2 different sheets. — Adam, Oct 07 '11 at 16:55

score 0 · Answer 1 · answered Oct 05 '11 at 19:06

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

Gaijinhunter · Accepted Answer · 2011-10-06T02:44:10.087

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...

score 0 · Answer 3 · edited May 08 '18 at 21:49

Two further options:

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

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

Looking for changes in cells - Excel files

3 Answers3