is it possible to write a macro to compare two spreadsheets within excel and highlight the differences?

[theNEOone]

i'm working with a lot of data that i have to manually compare at the end of each month. i'm working w/ a mortgage program that prints a report that lists the loans that we service. i take this list and compare it to an excel document, and manually highlight which loans have been added or removed, and then make the appropriate changes in the excel document.

previously, the mortgage report was handed to me, but i recently went into the program myself and realized that it is capable of exporting the list in excel format. i want to run a macro that will compare the imported spreadsheet to the previous month's spreadsheet and highlight the differences. both spreadsheets will have several columns, but for my purpose i only need to compare a single column in each document.

can this be done, and how? thanks.


=|

 

[KLin]

do a vlookup from this months column to last months column, and have it return either YES if it matches to last month's loans, or NO if it doesn't, then filter against the vlookup column for NO. That would probably be the best way to do it.

 

[theNEOone]

ok, i just realized that if i'm going to automate the comparison of the two worksheets, i might as well automate the entire process. although comparing the two documents is the most time consuming aspect of the process, i would benefit from automating the entire report.

in addition to comparing the column in the two worksheets (e.g. worksheet Bank1 and Bank2), i would like the macro to delete an entire row if the entry exists in Bank1 but not Bank2, but to copy the entry to Bank1 if it's in Bank2 but not Bank1.

i should add that i have never worked w/ visual basic or macros before, although i'm definitely willing to spend a considerable amount of time in learning how to fully utilize such tools. so although i would love some detailed instructions, i would also appreciate links to sites that give tutorials.


=|

 

[theNEOone]

also, the columns are not continuous. that is, the column that i want to compare (say, column B) has information (such as totals, column titles, etc.) that i don't want the macro to compare. would it be possible to run the macro on cells in the column formatted only in a specific way?


=|