Compare and Highlight Rows

[MHamid]

Hello,

I am trying to write a code where I can highlight a row based on a comparison of status for each account comparing data in two different sheets. I found the below code that I thought would work perfectly, but it's not highlighting my rows. Column 10 in both sheets (Current sheet and Prior Sheet) is the account number that it needs to find and then column 7 is the status. the Current Sheet will have an updated status. finding the matching account number in both sheet, once the match is found, then compare the status. If status has changed, then highlight entire row in the Current sheet. What am i missing in the code I tweaked below?

Sub HiLiteRows()
Dim rng1 As Range, rng2 As Range, cmp1 As Variant
Dim i As Long, j As Long, k As Long, op As Range
Dim lRow As Long, lRow2 As Long

'Set last row used
lRow = Sheets("Audit_Plan").Range("J" & Rows.Count).End(xlUp).Row
lRow2 = Sheets("Audit_Plan_PRIOR").Range("J" & Rows.Count).End(xlUp).Row

' Set this range to the table that you want hilighted
    Set rng1 = Sheets("Audit_Plan").Range("A7:CZ" & lRow)
' Set this range to the table you're comparing with
    Set rng2 = Sheets("Audit_Plan_PRIOR").Range("A7:CZ" & lRow2)
' Set this array to the columns you want to compare.  So here, we're comparing
' column 10 of Audit_Plan sheet with column 10 of Audit_Plan_PRIOR sheet, then column 7 of Audit_Plan sheet with column 7 of Audit_Plan_PRIOR sheet
' Add as many as you want
    cmp1 = Array(10, 10, 7, 7)
    
' Clear formatting
    rng1.Interior.Color = xlNone
' Set the output range to nothing
    Set op = Nothing
    
    d1 = rng1.Value
    d2 = rng2.Value
    For i = 1 To UBound(d1)
        For j = 1 To UBound(d2)
            For k = 0 To UBound(cmp1) - 1 Step 2
                If d1(i, cmp1(k)) <> d2(j, cmp1(k + 1)) Then Exit For
            Next k
            If k > UBound(cmp1) Then Exit For
        Next j
        If j > UBound(d2) Then
            If op Is Nothing Then
                Set op = rng1.Offset(i - 1).Resize(1)
            Else
                Set op = Union(op, rng1.Offset(i - 1).Resize(1))
            End If
        End If
    Next i
                
    If op Is Nothing Then Exit Sub
    op.Interior.Color = vbYellow
    
End Sub

 

[portews]

I think the problem starts with the d1 and d2. d1 and d2 are not declared as any particular type of variable, so by default they are variants. You're assigning them to the value of a range that is 104 columns wide, so there is no one single value for that range. I didn't bother trying to figure out what the next set of code was from there.

You didn't say so, but I'm assuming that each account number is unique, so there's only going to be one match.

Here's my take on it. It is the brute-force-and-ignorance method and not really elegant, but I've had mixed success with doing searches across worksheets, so this is my preferred method. It will look at a row in the new sheet and compare column 7 to column 7 in the prior sheet.

Sub HiLiteRows()
Dim rng1 As Range, rng2 As Range, cmp1 As Variant
Dim i As Long, j As Long, k As Long, op As Range
Dim lRow As Long, lRow2 As Long
Dim sht1 As Worksheet
Dim sht2 As Worksheet

Set sht1 = Sheets("Audit_Plan")
Set sht2 = Sheets("Audit_Plan_PRIOR")
'Set last row used
lRow = sht1.Range("J" & Rows.Count).End(xlUp).Row
lRow2 = sht2.Range("J" & Rows.Count).End(xlUp).Row

' Set this range to the table that you want hilighted
    Set rng1 = Sheets("Audit_Plan").Range("A7:CZ" & lRow)
' Set this range to the table you're comparing with
    'Set rng2 = Sheets("Audit_Plan_PRIOR").Range("A7:CZ" & lRow2)
' Set this array to the columns you want to compare.  So here, we're comparing
' column 10 of Audit_Plan sheet with column 10 of Audit_Plan_PRIOR sheet, then column 7 of Audit_Plan sheet with column 7 of Audit_Plan_PRIOR sheet
' Add as many as you want
    'cmp1 = Array(10, 10, 7, 7)
    
' Clear formatting
    rng1.Interior.Color = xlNone
' Set the output range to nothing
    'Set op = Nothing
    
'    d1 = rng1.Value
'    d2 = rng2.Value
    
'new loops
'for each row in the new sheet
    For i = 7 To lRow
        'loop through all the rows in the old sheet
        For j = 7 To lRow2
            'if you find a matching project
            If sht1.Cells(i, 10) = sht2.Cells(j, 10) Then
                'if the summaries don't match
                If Not sht1.Cells(i, 7) = sht2.Cells(j, 7) Then
                    'highlight that entire row on the new sheet.
                    sht1.Range("A" & i).EntireRow.Interior.Color = vbYellow
                End If
                'if there's only one match, you don't need to compare the rest
                'of the old sheet once you've found it. Go to the next line on the new sheet.
                Exit For
            End If
        Next j
    Next i
   
   ' If op Is Nothing Then Exit Sub
    'op.Interior.Color = vbYellow
End Sub