Compare Two Worksheets and Highlight Difference

Question

I have two worksheets that contain the same data points. I need to compare Sheet1 (valid data) to Sheet2 and see what was altered on Sheet2 by changing cell color to red. I tried this macro, but it painted everything, not just the differing cells. Below are images that show a small subset of data, in reality there are roughly 3K rows on each worksheet Image1 Image2

And this is the syntax I tried (that did not work due to highlighting everything almost)

Dim mycell As Range
Dim mydiff As Integer
Dim shtSheet1 As String
Dim shtSheet2 As String

shtSheet2 = "Sheet2"
shtSheet1 = "Sheet1"

For Each mycell In ActiveWorkbook.Worksheets(shtSheet2).UsedRange
  If Not mycell.Value = ActiveWorkbook.WOrksheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value Then
    mycell.Interior.Color = vbRed
    mydiffs = mydiffs+1
  End If
Next

EDIT
The suggestions below were still producing inaccurate results so I copied/pasted a few values into Notepad to compare, and I discovered that one sheet has some random spaces after the value which will cause the data from Sheet1 to Sheet2 to never be identical.

Does VBA have a TRIM() feature/function that could be added so random spaces at the end of the data will not matter in the comparison?

have you tried to replace (shtSheet1).Cells(mycell.Row ... with (shtSheet1).Range(mycell.Row ... ? — Doktor OSwaldo, May 03 '16 at 13:19

score 2 · Accepted Answer · answered May 03 '16 at 13:23

2

try with below

Sub checked()
    Dim mycell As Range
    Dim mydiff As Integer
    Dim shtSheet1 As Worksheet
    Dim shtSheet2 As Worksheet
    Set shtSheet1 = Worksheets("Sheet1")
    Set shtSheet2 = Worksheets("Sheet2")
    For Each mycell In shtSheet2.UsedRange
      If Not mycell.Value = shtSheet1.Cells(mycell.Row, mycell.Column).Value Then
        mycell.Interior.Color = vbRed
        mydiffs = mydiffs + 1
      End If
    Next
End Sub

answered May 03 '16 at 13:23

Karthick Gunasekaran

2,697
1
15
25

It seems my issue is stemming from an extra space or two being at the end of some of the values. Is there a TRIM() function in VBA that could be added to this syntax? – J-Nasty May 03 '16 at 13:27
Indeed there is a `Trim` function: `Trim(" Test ")` will result in "Test". – Ralph May 03 '16 at 13:44

Sun · Answer 2 · 2016-05-03T13:56:26.523

2

Why not try Conditional Formatting in Sheet2? The Formula could be

=A1<>Sheet1!A1

And set the fill colour to red under Format.

edited May 03 '16 at 13:56

answered May 03 '16 at 13:24

Sun

762
4
10

score 0 · Answer 3 · edited May 23 '17 at 11:52

0

Your answer seems to have been provided here: Find the differences between 2 Excel worksheets? Compare_excel or some other external Excel diff is what you're looking for.

If you have a small number of columns, you might want to try something like "=Sheet1!A1=Sheet2!A1" in another column of one of those sheets. Then, you can filter on FALSE entries.

edited May 23 '17 at 11:52

Community

1
1

answered May 03 '16 at 13:23

Weasemunk

455
4
16

Compare Two Worksheets and Highlight Difference

3 Answers3