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

[Spertyn]

Hello! I am having trouble wording this but the idea is pretty simple.
Table A:

Table B: (located on a different sheet)

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!

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book1
	A	B	C	D	E	F
1	Table A				Table B
2	A	xx	A		A	A
3	A	yy	B		A	C
4	B	zz	C		B	D
5	B	ww	D		C	X
6
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:C	Expression	=AND(COUNTIFS($E:$E,$A1,$F:$F,$C1)=0,$A1<>"",ROW()>1)	text	NO

 

[ranman256]

would this work:

How To Compare Rows From Two Sets Of Data In Excel

In this tutorial we are going to compare to data ranges that were supposed to be exact, but have some minor differences. We'll use the Go To Special command to help us highlight those differences.

excel-bytes.com

 

[Spertyn]

Welcome to the MrExcel forum!

Try:

Book1
	A	B	C	D	E	F
1	Table A				Table B
2	A	xx	A		A	A
3	A	yy	B		A	C
4	B	zz	C		B	D
5	B	ww	D		C	X
6
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:C	Expression	=AND(COUNTIFS($E:$E,$A1,$F:$F,$C1)=0,$A1<>"",ROW()>1)	text	NO

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!

 

[Eric W]

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:

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.

 

[Spertyn]

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:

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!

 

[Eric W]

My last question is, is there a way to make it ignore capitalization?

Sure, change this line:

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

to

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