Compare Two Excel Sheets – Highlight Differences

Sub test()
Dim varSheetA As Variant
Dim varSheetB As Variant
Dim strRangeToCheck As String
Dim iRow As Long
Dim iCol As Long
Set varSheetA = Sheet1

strRangeToCheck = "A1:B65536"
' If you know the data will only be in a smaller range, reduce the size of the ranges above.
Debug.Print Now
varSheetA = Worksheets("Sheet1").Range(strRangeToCheck)
varSheetB = Worksheets("Sheet2").Range(strRangeToCheck) ' or whatever your other sheet is.
Debug.Print Now

For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
    For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
        If varSheetA(iRow, iCol) = varSheetB(iRow, iCol) Then
            ' Cells are identical.
            ' Do nothing.
        Else
            ' Cells are different.
            ' Code goes here for whatever it is you want to do.   
        End If
    Next iCol
Next iRow
End Sub

Please find the above code. I need to highlight the different cells. I tried as mentioned below comments issue has not resolved. Please any one help me on this!

CodePudding user response：

varSheetA is an array that holds the values of the range, it doesn't know of the cells on the sheet.

(If varSheetA should be the range then you would have to write set varSheetA = ... plus defining the variable as range)

worksheets("Sheet1").cells(iRow, iCol).color = vbRed

should work

BTW: it is much easier for us to help if you post the code itself and not a picture - how else should we "take the code"?

CodePudding user response：

What about:

varSheetA(iRow, iCol).Color = RGB(0,255,0)

When you take into account that 255,0,0 is red and 0,255,0 is green, that should be very visible.