Compare same Column in 2 Workbooks
 

I have 2 workbooks and want to compare Column A in both books. If there
is no match for an item in WB1 Column A to WB2 Column A, I want to
highlight or insert a cheater column in WB1 indicating "not in WB2".
Similarly, if something is found in WB2 and not in WB1, highlight in a
different color or "not in WB1". TIA

Greg

Compare same Column in 2 Workbooks
 

You don't need to write a macro to do this... you could do it with the
VLookup function or the index and Match...

i.e. insert column B of WB1 copy down for length of data in the column
=if(type(Vlookup(a1,[WB2.xls]Sheet1!$A:$A,1,False))=16,"Not in
WB2","")

repeat for wb2 as well should should give you want...

either that or a function like this...

Sub CompareColumns()
Dim wb1 as workbook
dim wb2 as workbook
dim ws1 as worksheet
dim ws2 as worksheet
dim rng1 as range
dim rng2 as range
dim c1 as range
dim c2 as range
dim bfound as boolean

set wb1 = Workbooks("WB1")
set wb2 = Workbooks("WB2")
set ws1 = wb1.activesheet
set ws2 = wb2.activesheet
set rng1 = ws1.range(cells(1,1),cells(ws1.usedrange.rows.coun t, 1))
Set rng2 = ws2.range(cells(1,1),cells(ws2.usedrange.rows.coun t, 1))

for each c1 in rng1
if c1 < "" then
bfound = false
for each c2 in rng2
if c1.value = c2.value then
bfound = true
end if
next
if not bfound then
range(c1.offset(0,1).address).Value = "Not in WB2"
end if
end if
next
for each c2 in rng2
if c2 < "" then
bfound = false
for each c1 in rng1
if c1.value = c2.value then
bfound = true
end if
next
if not bfound then
range(c2.offset(0,1).address).value = "Not in WB1"
end if
end if
next
end sub


--
kraljb
------------------------------------------------------------------------
kraljb's Profile: http://www.excelforum.com/member.php...fo&userid=9955
View this thread: http://www.excelforum.com/showthread...hreadid=400966

Compare same Column in 2 Workbooks
 

Kraljb, thank for your formula, but when I try your sub, I get an error
on the line set range:

set rng1 = ws1.range(cells(1,1),cells(ws1*.usedrange.rows.cou nt, 1))
Set rng2 = ws2.range(cells(1,1),cells(ws2*.usedrange.rows.cou nt, 1))

Thanks

Greg