Comparing two lists, identifying and highlighting missing entries using MATCH

[mrdgaf]

Hi everyone, I have scoured the net to try this my problem.

I have several lists that need to be compared for reconciliation purposes. Certain criteria need to match off between two rows before the next row is searched for, hence the first 5 variables and the how the value of these variables will change within a loop. The most important thing is that a single positive match does not cause multiple positive matches - some of these lists will have the same rows multiple times, so my idea is to delete matching rows until you are left with the undeleted/unmatched rows using loops. And then the process is repeated in reverse to identify mismatches in the other list. Ideally, there would be no need to delete anything and unmatched entries would just be highlighted if someone has such an elegant solution on hand.

Anyway, I have used the CSE match array formula within the sheet that works fine. But then I can't get it to work in VBA using the many examples online for using MATCH and EVALUATE. If someone could please help, that would be much appreciated.

You can see all the attempts I've made to try and get it to work at the bottom as comments.

Sub DeleteMatches()
    ' Declare variables
    Dim Direction As Variant
    Dim OrderType As Variant
    Dim Amount As Variant
    Dim CCY As Variant
    Dim Rate As Variant
    Dim RowCt As Integer
    Dim Formula As Variant
    Dim iRow As Integer
    Dim colNum As Variant
    Sheets("Example").Select
    RowCt = Cells(Rows.Count, 11).End(xlUp).Row - 1 'creating variable for toral rows to cycle through
    iRow = 2
    Direction = Range("K" & iRow)
    OrderType = Range("L" & iRow)
    Amount = Range("M" & iRow)
    CCY = Range("N" & iRow)
    Rate = Range("P" & iRow)
    Formula = "MATCH(1,((B:B="" & OrderType & "")*(C:C="" & Direction & "")*(D:D="" & Amount & "")*(E:E="" & CCY& "")*(H:H="" & Rate & "")),0)"
    RowDelete = Evaluate(Formula)
    MsgBox RowDelete
    ' colNum = Application.Match(1,((B1:B2=OrderType)*(C1:C2=Direction)*(D:D=Amount)*(E:E=CCY)*(H:H=Rate)),0)
    ' Formula = "MATCH(1,((B:B=OrderType)*(C:C=Direction)*(D:D=Amount)*(E:E=CCY)*(H:H=Rate)),0)"
    ' Formula = "MATCH(1,((B:B=L2)*(C:C=K2)*(D:D=M2)*(E:E=N2)*(H:H=P2)),0)"
    ' colNum = Worksheets("Example").Evaluate("MATCH(1,((B:B=OrderType)*(C:C=Direction)*(D:D=Amount)*(E:E=CCY)*(H:H=Rate)),0)")
End Sub