Untitled

Sub HighlightModifiedDataRef()

' Declare variables for the sheets
Dim sheet1 As Worksheet, sheet2 As Worksheet
Set sheet1 = ThisWorkbook.Sheets("Sheet1") 'change this to the name of your first sheet
Set sheet2 = ThisWorkbook.Sheets("Sheet2") 'change this to the name of your second sheet

' Define the range of cells to compare
Dim rng1 As Range, rng2 As Range
Set rng1 = sheet1.UsedRange ' get all used cells in first sheet

' loop through each row in the first sheet, start from row 7
For i = 7 To rng1.Rows.Count
    ' get the value in column B of the current row
    Dim refValue As String
    refValue = sheet1.Cells(i, 2).Value

    ' get the corresponding row in the second sheet
    Set rng2 = sheet2.Range("B:B").Find(What:=refValue, LookIn:=xlValues, LookAt:=xlWhole)
    If Not rng2 Is Nothing Then
        'loop through each column, except column B
        For j = 1 To rng1.Columns.Count
            If j <> 2 Then
                'check if the value of the cell in the first sheet is different than the value in the second sheet
                If CStr(sheet1.Cells(i, j).Value) <> CStr(sheet2.Cells(rng2.Row, j).Value) Then
                    ' if the value is different, highlight in light red color
                    sheet1.Cells(i, j).Interior.Color = RGB(255, 150, 150)
                End If
            End If
        Next j
    End If
Next i

End Sub