Highlight table rows with matches in another table

[Samson92]

Hi,

I have a workbook with 2 sheets, Old and New. Every so often I want to compare sets of data based on values in 2 columns, and if they match, highlight the entire row. The tables are laid out the same;
Column A (Client) - Column B - Column C - Column D (Preparer) - Column E - Column F - Column G - Column H

What I want to do, is see which clients are still on the new list. This is done by seeing if the Client Name (Column A) and the Preparer (Column D) from the New data is still on the Old data, and if so, highlight the row on the Old tab.

I've been trying using conditional formatting, but it's mostly looking for other columns too, which need to be there, but just not helpful for this. Thanks.

 

[Trebor76]

Hi Samson92,

Try this:

Option Explicit
Sub Macro1()

    Dim wsNew As Worksheet
    Dim wsOld As Worksheet
    Dim rngMyCell_New As Range, rngMyRange_New As Range
    Dim rngMyCell_Old As Range, rngMyRange_Old As Range
    Dim strMyKey As String
    
    Application.ScreenUpdating = False
    
    Set wsNew = ThisWorkbook.Sheets("New")
    Set wsOld = ThisWorkbook.Sheets("Old")
    
    For Each rngMyCell_New In wsNew.Range("A2:A" & wsNew.Range("A:D").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row)
        If Len(wsNew.Range("A" & rngMyCell_New.Row)) > 0 And Len(wsNew.Range("D" & rngMyCell_New.Row)) > 0 Then
            strMyKey = StrConv(wsNew.Range("A" & rngMyCell_New.Row), vbUpperCase) & StrConv(wsNew.Range("D" & rngMyCell_New.Row), vbUpperCase)
            For Each rngMyCell_Old In wsOld.Range("A2:A" & wsOld.Range("A:D").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row)
                If strMyKey = StrConv(wsOld.Range("A" & rngMyCell_Old.Row), vbUpperCase) & StrConv(wsOld.Range("D" & rngMyCell_Old.Row), vbUpperCase) Then
                    wsOld.Range("A" & rngMyCell_Old.Row & ":H" & rngMyCell_Old.Row).Interior.Color = RGB(0, 255, 0) 'Colour the matching row(s) green. Change to suit.
                End If
            Next rngMyCell_Old
        End If
    Next rngMyCell_New
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[Peter_SSs]

Is this what you mean?

Book1
	A	B	C	D	E	F	G	H
1	Client	Hdr 2	Hdr 3	Preparer	Hdr 5	Hdr 6	Hdr 7	Hdr 8
2	Client 1	data	data	Preparer 4	data	data	data	data
3	Client 23	data	data	Prepare 2	data	data	data	data
4	Client 5	data	data	Preparer 8	data	data	data	data
5	Client 6	data	data	Preparer 9	data	data	data	data
6	Client 7	data	data	Preparer 10	data	data	data	data
7	Client 8	data	data	Preparer 11	data	data	data	data
New

Book1
	A	B	C	D	E	F	G	H
1	Client	Hdr 2	Hdr 3	Preparer	Hdr 5	Hdr 6	Hdr 7	Hdr 8
2	Client 1	data	data	Preparer 1	data	data	data	data
3	Client 2	data	data	Preparer 2	data	data	data	data
4	Client 3	data	data	Preparer 3	data	data	data	data
5	Client 1	data	data	Preparer 4	data	data	data	data
6	Client 2	data	data	Preparer 5	data	data	data	data
7	Client 3	data	data	Preparer 6	data	data	data	data
8	Client 4	data	data	Preparer 7	data	data	data	data
9	Client 5	data	data	Preparer 8	data	data	data	data
10	Client 6	data	data	Preparer 9	data	data	data	data
11	Client 4	data	data	Preparer 10	data	data	data	data
12	Client 5	data	data	Preparer 11	data	data	data	data
13	Client 6	data	data	Preparer 12	data	data	data	data
Old

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:H15	Expression	=COUNTIFS(New!$A$2:$A$100,$A2,New!$D$2:$D$100,$D2)	text	NO

 

[Samson92]

Thanks @Peter_SSs that's exactly what I was looking for. I had a similar formula, but I had it the wrong way round, this works perfect.

Would it be possible to change it, so that instead of highlighting the cells that meet the criteria, it highlights every one that doesn't instead?

That might be useful for another part of the workbook. Thanks again for your help.

 

[Peter_SSs]

Would it be possible to change it, so that instead of highlighting the cells that meet the criteria, it highlights every one that doesn't instead?

Just add =0 to the formula
=COUNTIFS(New!$A$2:$A$100,$A2,New!$D$2:$D$100,$D2)=0

However, if there are any rows that are blank in columns A & D it would highlight those too. If that is your circumstance & you don't want blanks highlighted, post back for a further modification.

 

[Samson92]

I do have blank rows, would it be possible to ignore them?

Thanks.

 

[Peter_SSs]

I do have blank rows, would it be possible to ignore them?

Sure ..

=AND($A2<>"",$D2<>"",COUNTIFS(New!$A$2:$A$100,$A2,New!$D$2:$D$100,$D2)=0)

 

[Samson92]

Perfect. Thank you for your help!

 

[Peter_SSs]

Perfect. Thank you for your help!

You're welcome. Thanks for the follow-up.