Compare two lists

[BrianAgBe]

Hi there!
I am trying to do this way (https://www.excel-easy.com/examples/compare-two-lists.html) to compare in VBA but I can not get the same result as when I try it manually.

 

[Coding4Fun]

Try this

Sub Compare2Lists()


    ' Get the last row of your lists - assuming in Column A and B - Change accordingly
    L1LR = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    L2LR = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
    ' Set Named Ranges firstList and secondList
    ActiveWorkbook.Names.Add Name:="firstList", RefersTo:="=Sheet1!$A$1:$A$" & L1LR
    ActiveWorkbook.Names.Add Name:="secondList", RefersTo:="=Sheet1!$B$1:$B$" & L1LR
    ' Add Conditional Formatting on firstList
    Range("A1:A" & L1LR).FormatConditions.Add Type:=xlExpression, Formula1:= "=COUNTIF(secondList,A1)=0"
    Range("A1:A" & L1LR).Select
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    ' Set Color 1
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    ' Add Conditional Formatting on secondList
    Range("B1:B" & L2LR).FormatConditions.Add Type:=xlExpression, Formula1:= "=COUNTIF(firstList,B1)=0"
    Range("B1:B" & L2LR).Select
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    ' Set Color 2
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False


End Sub

 

[BrianAgBe]

Thank you so much. It was very useful for my task.

Best regards!

 

[Coding4Fun]

Thank you so much. It was very useful for my task.

Best regards!

Glad it worked for you, just in case you did not catch it, there was a typo in the previous code. My apologies, I just saw it when reading your reply.

[SIZE=3]Sub Compare2Lists()

    ' Get the last row of your lists - assuming in Column A and B - Change accordingly
    L1LR = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    L2LR = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
    ' Set Named Ranges firstList and secondList
    ActiveWorkbook.Names.Add Name:="firstList", RefersTo:="=Sheet1!$A$1:$A$" & L1LR
[U][B]ActiveWorkbook.Names.Add Name:="secondList", RefersTo:="=Sheet1!$B$1:$B$" & [COLOR=#ff0000]L2LR[/COLOR][/B][/U]
    ' Add Conditional Formatting on firstList
    Range("A1:A" & L1LR).FormatConditions.Add Type:=xlExpression, Formula1:= "=COUNTIF(secondList,A1)=0"
    Range("A1:A" & L1LR).Select
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    ' Set Color 1
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    ' Add Conditional Formatting on secondList
    Range("B1:B" & L2LR).FormatConditions.Add Type:=xlExpression, Formula1:= "=COUNTIF(firstList,B1)=0"
    Range("B1:B" & L2LR).Select
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    ' Set Color 2
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

End Sub[/SIZE]