Excel Compare Macro

[tdpman]

Hi guys, I was wondering if one or more of you might be able to help me. I need a macro that will compare column C in 2 seperate workbooks (workbook A & B) and change the font color of any values in workbook A that are not in workbook B. Basically I have a report that is run daily listing several orders. Orders are added to this report daily and I need to have the NEW orders highlighted. Hope that makes sense. Any help you can give would be most appreciated.

Thanks again!

 

[xlbo]

Try this - both workbooks must be open and you'll need to change the sheet names I imagine:

Sub FindNewOrders()
dim nSht as worksheet, oSht as worksheet, lRow as long
dim fWhat as string
set nSht = workbooks("A&quot.sheets("Sheet1&quot
set oSht = workbooks("B&quot.sheets("Sheet1&quot
lRow = nSht.range("C" & rows.count).end(xlup).row
for each c in nsht.range("C2:C" & lRow)
fWhat = c.text
with oSht.columns("C&quot
set fCell = .find(fWhat,lookin:=xlvalues,lookat:=xlwhole)
end with
if not fCell is nothing then
c.interior.colorindex = 3
else
end if
next
end sub

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[tdpman]

I'll try that and see how it works. One question though, is there anyway to write it to where I wouldn't have to change the names of the workbook? I'm going to be using this everyday on workbooks that change names everday. Example: they change by date from "Report042303.xls" to "Report042403.xls". Any ideas?

 

[xlbo]

Well, as long as it is always "Report" & Todays Date
compared to "Report" & Yesterdays Date then summat like:

tDate = format(now(),"dd/mm/yy&quot
if weekday(now())<>2 then
lDate = format(val(now())-1,&quot;dd/mm/yy&quot
else
lDate = format(val(now())-3,&quot;dd/mm/yy&quot

set nSht = workbooks(&quot;Report&quot; & tDate).sheets(&quot;Sheet1&quot
set oSht = workbooks(&quot;Report&quot; & lDate).sheets(&quot;Sheet1&quot


Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[KenWright]

Couple of ways (Posted previously by Gord Dibben in msnews):-

Myrna Larson and Bill Manville have written an add-in named Compare.xla that
is very hady for this type of problem. Can be downloaded in *.ZIP form from
Chip Pearson's site.

http://www.cpearson.com/excel/download.htm

An alternative is Rob Bruce's CompareSheets.xla. Basically the same as above
except the differences are flagged rather than displayed on a new worksheet.

Can be downloaded as cmprshts.zip from Rob's site.

http://www.rb-ad.dircon.co.uk/rob/excelvba/utils/index.htm

Regards
Ken................