Macro to compare two excel files and highlight the differences

I have an excel file that I keep that contains demographic information such as Name, unit number, email, phone number. Each month I get a new copy of the document that has updated information. I currently have to go through line by line checking for changes from the old list to the new list. I would like a macro that will just highlight the updated information in the new excel file.

The thing that for sure stays the same each month is the Unit Number but any of the other information might change.

Can anyone help create a Macro that I can use to do the dirty work for me?

Any help is much appreciated

I just did something similar here:

http://www.excelforum.com/excel-prog...g-problem.html

It looks at a key value, then matches items in each column. Yours is a tad different, you probably want to note items that have been added/removed as well.

Can you post some more info? Which column has your key (Unit)? Do you actually get adds/deletes? Which columns contain data? Is the incoming information in the same workbook, in a different worksheet, or a different workbook?

The Unit data is in Column A
The unit numbers are always the same but the resident sometimes changes or an existing resident will change a phone number or something.
Columns are A through G
Unit # First Name Last Name E-mail Cell Phone Home Phone Work Phone

The information comes in a separate file/workbook but it is ordered the same way

I'd like a macro to compare the old file to the new one and just highlight anything that is different in the new one.
I'm not even sure if that's possible

I tweaked the procedure a bit. Go to the VBA IDE (alt-F11), insert a new module and paste in this code:

Please Login or Register  to view this content.

You will need to change the worksheet names to match yours (Sheet1 in the main workbook, Sheet1 in the comparing workbook), the ranges point to the top rows of your data (A2:G2). It will highlight the changes in the new workbook.

I really appreciate your help! I think I must be doing something wrong though because I can't make it happen. Perhaps it's with the place I'm pasting the code or I am not opening up my workbooks correctly. I don't know perhaps I'm not savvy enough for macros At any rate thanks very much for helping me maybe I will keep playing with it until I figure it out

Walleye:

Looking for some guidance along same lines as the questions posed by "neets1228". I have two spreadsheets containing stock positions with column "A" having the account number and Column "B" the stock symbol. I am looking to identify any new positions that have been added. Note the at the account number can appear multiple times.

Example:
Account Symbol/Cusip
163-107589 COV
163-107589 STX
163-107589 WFT
163-107589 TEL
163-107589 TYC
163-107822 COV
163-107822 SHPG
163-108337 RBA
163-108338 ASML
163-108338 AONNY
163-108338 AGU
163-108338 ASGLY
163-108338 AZN

Thank you for any insight.
Nick

Just to help out a bit, the code first prompts for a file to open, storing the file's path in strPath. It then opens the file, and set reference ranges on both the opened file (rng2) and the main file (rng1). You can change the references here to point to the proper starting ranges in each respective workbook.

Next, it looks at the first column of data from the main file to find the number of rows of data, and puts the entire data set into an array (arr1). With the data in arr1, it creates an index of the values in the first column (probably column A), for searching on later.

Then, it looks at the first column of data from the opened file to find the number of rows of data, and puts the entire data set from the opened file into an array (arr2). I resets the highlighting on the opened file, then loops through the values from the first column, checking to see if they match a value from the main workbook. When a match is found, it compares the values on that row with values from the main workbook, and highlights cells that are different. If a match is not found, it highlights the entire row.

The rest is just clean-up...

It probably would have been faster to get a new thread, but here is the code tweaked:

Please Login or Register  to view this content.

It does pretty much the same thing, but combines columns A & B into the index, then highlights the entire row if a match is not found.

thank you for your input