Use VBA to highlight difference in columns

[GloriaKo]

I usually have to compare 2 columns of data.......and I need to find out which data in the first column are absent in the second column and vice versa.

Example:
A B
30554 21535
30552 30554
30568 30568

I want to use VBA to automatically highlight the difference as shown above.......if possible, I would like to use InputBox to select the data range....Thks

 

[rs2k]

Have a look at this link;

http://www.mrexcel.com/forum/showthread.php?t=503721

I have another way which uses a mix of formulas and vba.

HTH
Colin

 

[MickG]

Try this:-

[COLOR=navy]Sub[/COLOR] MG09Nov49
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] k
[COLOR=navy]On[/COLOR] [COLOR=navy]Error[/COLOR] [COLOR=navy]Resume[/COLOR] [COLOR=navy]Next[/COLOR]
 [COLOR=navy]Set[/COLOR] Rng = Application.InputBox(prompt:="Please [COLOR=navy]Select[/COLOR] ", Title:="Colour Uniques", Type:=8)
[COLOR=navy]If[/COLOR] Rng [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR] [COLOR=navy]Exit[/COLOR] [COLOR=navy]Sub[/COLOR]
[COLOR=navy]On[/COLOR] [COLOR=navy]Error[/COLOR] GoTo 0
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
        [COLOR=navy]If[/COLOR] Not .Exists(Dn.Value) [COLOR=navy]Then[/COLOR]
            .Add Dn.Value, Dn
        [COLOR=navy]Else[/COLOR]
            [COLOR=navy]If[/COLOR] Not Dn.Column = .Item(Dn.Value).Column [COLOR=navy]Then[/COLOR]
            [COLOR=navy]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR]
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] k [COLOR=navy]In[/COLOR] .Keys
    [COLOR=navy]If[/COLOR] .Item(k).Count = 1 [COLOR=navy]Then[/COLOR]
        [COLOR=navy]If[/COLOR] .Item(k).Column = Rng(1, 1).Column [COLOR=navy]Then[/COLOR] .Item(k).Interior.ColorIndex = 34
        [COLOR=navy]If[/COLOR] .Item(k).Column = Rng(1, 2).Column [COLOR=navy]Then[/COLOR] .Item(k).Interior.ColorIndex = 35
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] k
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick