Highlight row if it does not match any rows on another sheet

Spertyn

New Member
Joined
Jul 30, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello! I am having trouble wording this but the idea is pretty simple.
Table A:
1627656611446.png

Table B: (located on a different sheet)
1627656633249.png


I would like Row 2 in Table A to be highlighted because it does not match either row in Table B.
I will need it to be able to compare certain cells because in my actual table, the values do not line up next to each other like they are in this example.

Any help will be much appreciated! If I need to clarify anything please let me know!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Welcome to the MrExcel forum!

Try:

Book1
ABCDEF
1Table ATable B
2AxxAAA
3AyyBAC
4BzzCBD
5BwwDCX
6
Sheet3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A:CExpression=AND(COUNTIFS($E:$E,$A1,$F:$F,$C1)=0,$A1<>"",ROW()>1)textNO
 
Upvote 0
Solution
Welcome to the MrExcel forum!

Try:

Book1
ABCDEF
1Table ATable B
2AxxAAA
3AyyBAC
4BzzCBD
5BwwDCX
6
Sheet3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A:CExpression=AND(COUNTIFS($E:$E,$A1,$F:$F,$C1)=0,$A1<>"",ROW()>1)textNO
Hello!
This is perfect! Exactly what I was looking for. I'm not sure if there is a fix for this but due to the size of my spreadsheet (900+ rows on multiple sheets) It drastically slows down everything. Even scrolling through is a challenge now. Perhaps there is a way to do this once and leave the rows highlighted so it isn't constantly updating and slowing everything down?
Thank you very much!
 
Upvote 0
Sure, we can write a macro to do this. Remove the Conditional Formatting. Open a COPY of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. Paste the following code into the window that opens:

VBA Code:
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

' Set this range to the table that you want hilighted
    Set rng1 = Sheets("Sheet3").Range("A2:C5")
' Set this range to the table you're comparing with
    Set rng2 = Sheets("Sheet3").Range("E2:F4")
' Set this array to the columns you want to compare.  So in this example, we're comparing
' column 1 of table1 with column 1 of table2, then column 3 of table1 with column 2 of table2
' Add as many as you want
    cmp1 = Array(1, 1, 3, 2)
    
' 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 = vbCyan
    
End Sub

In the top section, change the 2 range variables to the ranges of your tables. Changes the values in the compare array so that you're comparing the right columns. Then close the VBA editor with Alt-Q. In Excel, press Alt-F8 to open the macro selector, select HiLiteRows, and click Run.

Let us know how this works.
 
Upvote 0
Sure, we can write a macro to do this. Remove the Conditional Formatting. Open a COPY of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. Paste the following code into the window that opens:

VBA Code:
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

' Set this range to the table that you want hilighted
    Set rng1 = Sheets("Sheet3").Range("A2:C5")
' Set this range to the table you're comparing with
    Set rng2 = Sheets("Sheet3").Range("E2:F4")
' Set this array to the columns you want to compare.  So in this example, we're comparing
' column 1 of table1 with column 1 of table2, then column 3 of table1 with column 2 of table2
' Add as many as you want
    cmp1 = Array(1, 1, 3, 2)
   
' 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 = vbCyan
   
End Sub

In the top section, change the 2 range variables to the ranges of your tables. Changes the values in the compare array so that you're comparing the right columns. Then close the VBA editor with Alt-Q. In Excel, press Alt-F8 to open the macro selector, select HiLiteRows, and click Run.

Let us know how this works.
Hello again!
So I finally got a chance to test this out today. It works wonderfully. I am having some implementation issues but those are mostly errors on my side that I need to fix.
My last question is, is there a way to make it ignore capitalization? No big deal if it cant, It would just be nice.

Thank you so much for the help!
 
Upvote 0
My last question is, is there a way to make it ignore capitalization?
Sure, change this line:

Excel Formula:
If d1(i, cmp1(k)) <> d2(j, cmp1(k + 1)) Then Exit For

to

Excel Formula:
If Lcase(d1(i, cmp1(k))) <> Lcase(d2(j, cmp1(k + 1))) Then Exit For
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top