vbaexcelexcel-2013

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?


Solution

  • 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