Hello and thank you for your help.

I have a macro that compares 2 lists and highlights the unique values in column A after comparing them to column B. This macro only being some what basic has a flaw.

It works perfectly until a unique value is added into column B at that point it stops working where it highlights the whole of column A red underneath the unique number in column B which is not what I need to happen.

So my hope is that some one can help me solve this issue and help me make it also highlight unique values in column B in a different colour as this macro is used to check hundreds of values at a time and some are duplicates. I used to just use the remove duplicates tool but because of the fact that in my lists that there could be more than one of the same numbers this did not work often leading to mistakes.

This is the macro I use

Sub CompareLists()
    Dim r1 As Long
    Dim m1 As Long
    Dim r2 As Long
    Dim m2 As Long
    m1 = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1:A" & m1).Interior.ColorIndex = xlColorIndexNone
    r1 = 1
    r2 = 1
    Do While Range("A" & r1).Value <> ""
        If Range("B" & r2).Value = Range("A" & r1).Value Then
            r2 = r2 + 1
        Else
            Range("A" & r1).Interior.Color = vbRed
        End If
        r1 = r1 + 1
    Loop
End Sub
Once again thank you for your help