Compare two sheets, highlight differences

It means you don't need extra column with formula to check the difference?

Hi papasmurfuo9,

Try this:

Option Explicit
Sub Macro1()

    Const lngStartRow As Long = 2 'Starting row number for two datasets. Change to suit.

    Dim lngMyRow As Long
    Dim lngEndRow As Long
    Dim lngMatchRow As Long
    Dim lngMyCol As Long
    Dim strMyCol As String
    
    Application.ScreenUpdating = False
    
    lngEndRow = Sheets("Sheet2").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    For lngMyRow = lngStartRow To lngEndRow
        If IsError(Evaluate("MATCH(Sheet2!A" & lngMyRow & ",Sheet1!A:A,0)")) = False Then
            lngMatchRow = Evaluate("MATCH(Sheet2!A" & lngMyRow & ",Sheet1!A:A,0)")
            For lngMyCol = 1 To 24
                strMyCol = Left(Cells(1, lngMyCol).Address(True, False), Application.WorksheetFunction.Search("$", Cells(1, lngMyCol).Address(True, False)) - 1)
                If Sheets("Sheet2").Range(strMyCol & lngMyRow).Value <> Sheets("Sheet1").Range(strMyCol & lngMatchRow).Value Then
                    Sheets("Sheet2").Range(strMyCol & lngMyRow).Interior.Color = RGB(255, 0, 0)
                End If
            Next lngMyCol
        Else
            Sheets("Sheet2").Range("A" & lngMyRow & ":X" & lngMyRow).Interior.Color = RGB(255, 255, 0)
        End If
    Next lngMyRow
    
    Application.ScreenUpdating = False

End Sub

Regards,

Robert

See how file attached works

your code works great, just the only problem i have if is the product codes are not in the same order on both sheets which is very possible

The order shouldn't matter as the code finds what row on Sheet1 the product code is on and goes from there

I'm glad PCI was able to provide you with a solution.

Robert