VBA Dictionary mumps Code highlight both non matching columns

[Stephen_IV]

https://www.mrexcel.com/forum/excel-questions/1054389-compare-two-column.html

Based off of the thread above, how do I get the code to highlight both columns if they do not match.

mumps code:

Sub CompareLists()
    Application.ScreenUpdating = False
    Dim Rng As Range, RngList As Object
    Set RngList = CreateObject("Scripting.Dictionary")
    For Each Rng In Range("B1", Range("B" & Rows.Count).End(xlUp))
      If Not RngList.Exists(Rng.Value) Then
        RngList.Add Rng.Value, Nothing
      End If
    Next Rng
    For Each Rng In Range("A1", Range("A" & Rows.Count).End(xlUp))
      If Not RngList.Exists(Rng.Value) Then
        Rng.Font.ColorIndex = 3
      End If
    Next Rng
    RngList.RemoveAll
    Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

Is this what you mean?

If so, could you just apply the shown Conditional Formatting directly? If not, there is code below to apply the formatting.

Excel Workbook
	A	B
1	Employee	Manager
2	Ken	Sue
3	Ann	Sue
4	Jen	Pam
5	Jim	Kim
6	Tom	Ted
7	Ann	Ted
8	Bill	Ben
9	Ann	Sue
10	Jim	Ted
11	Ken	Sue
12	Jim	Tom
13	Jen	Pam
14	Ken	Sue
15	Pat	Peg
Managers do not match

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =COUNTIF($A$2:$A$15,$A2)<>COUNTIFS($A$2:$A$15,$A2,$B$2:$B$15,$B2)	Abc

Sub DifferentManagers()
  With Range("A2", Range("B" & Rows.Count).End(xlUp))
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:=Replace(Replace(Replace(Replace( _
      "=COUNTIF(#,^)<>COUNTIFS(#,^,%,@)", "#", .Columns(1).Address), "^", .Cells(1, 1).Address(0, 1)), "%", .Columns(2).Address), "@", .Cells(1, 2).Address(0, 1))
    .FormatConditions(1).Interior.Color = vbYellow
  End With
End Sub

 

[Stephen_IV]

Good morning Peter_SSs,
Thanks for responding. What I am looking to do is through the dictionary highlight values that are in Column A that are not in Column B and likewise Column B that were in Column A. Please see below.
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>List 1</th><th>List 2</th></tr></thead><tbody>
<tr><td>Peter</td><td><font color="blue">Paul</td></tr>
<tr><td><font color="blue">Ken</td><td><font color="blue">Mike</td></tr>
<tr><td><font color="blue">Ray</td><td>Jay</td></tr>
<tr><td>Jay</td><td>Peter</td></tr>
</tbody></table>

 

[Peter_SSs]

Thanks for responding. What I am looking to do is through the dictionary highlight values that are in Column A that are not in Column B and likewise Column B that were in Column A.

Hmm, not too much like that other thread then.

Why are you insisting on using the dictionary object, when Excel's standard Conditional Formatting can do the same job?

Excel Workbook
	A	B
1	List 1	List 2
2	Peter	Paul
3	Ken	Mike
4	Ray	Jay
5	Jay	Peter
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =ISNA(MATCH(A2,B$2:B$5,0))	Abc
B2	1. / Formula is =ISNA(MATCH(B2,A$2:A$5,0))	Abc

 

[Stephen_IV]

Hi Peter_SSs thanks for that. I am locked into the dictionary object because I am trying to learn VBA. Thank you for the reponse.

 

[Peter_SSs]

I am locked into the dictionary object because I am trying to learn VBA.

OK, now I understand - that's a fair enough reason.
Here is one way. It is just designed for a 2-column range like you example.

Sub MarkSingles()
  Dim d As Object
  Dim a As Variant
  Dim i As Long, j As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  With Range("A2", Range("B" & Rows.Count).End(xlUp))
    .Font.Color = 0
    a = .Value
    For i = 1 To UBound(a, 1)
      For j = 1 To 2
        d(a(i, j)) = d(a(i, j)) & j
      Next j
    Next i
    For i = 1 To UBound(a, 1)
      For j = 1 To 2
        If InStr(1, d(a(i, j)), 3 - j) = 0 Then .Cells(i, j).Font.Color = vbBlue
      Next j
    Next i
  End With
End Sub

 

[Stephen_IV]

Thank you so much Peter_SSs!! I an very appreciative for your help and kindness!

 

[billandrew]

This is awesome. I am also trying to learn more. How does one go about proceeding?

 

[Peter_SSs]

Thank you so much Peter_SSs!! I an very appreciative for your help and kindness!

No problem - glad to help.

 

[Peter_SSs]

This is awesome. I am also trying to learn more. How does one go about proceeding?

Spend more time in this forum.