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