CODEWITHSUNDEEP
vbaexcelexcel-2013
J-Nasty
J-Nasty

Reputation: 47

Compare Two Worksheets and Highlight Difference

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?

Upvotes: 3

Views: 12197

Answers (3)

Sun
Sun

Reputation: 762

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

=A1<>Sheet1!A1

And set the fill colour to red under Format.

Upvotes: 2

Weasemunk
Weasemunk

Reputation: 455

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.

Upvotes: 0

Karthick Gunasekaran
Karthick Gunasekaran

Reputation: 2713

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

Upvotes: 2

Related Questions