Compare between 2 excel files and identify similarity and differences

Hi,

I wonder if someone could help me compare 2 files with 5 columns of information. I'd like to first identify and highlight the data on the first column, under POINT NUMBER, and highlight if spreadsheet2 has the same information as spreadsheet1. If it's easier, I could copy the information and only compare between sheet1 and sheet2.

In addition to highlighting the same information on sheet1, I'd like to also highlight the information that's different for columns B, C, D and E. Basically, if the information on sheet1, column A, has point string, LE_000_OCD_27007 on row 8, and if sheet2 has the same information on column A, but on a different row, then I'd like the routine to compare the rest of the columns for differences.

The column headers would be:

POINT NUMBER, Y, X, Z, DESCRIPTION.

Point number and description contains alphanumeric info.
X, Y and Z contains only numbers.

Thank you.

Hi and welcome
please post a sample sheet with some data and expected results ( click " Go advanced - Manage attachments). Thanks

Thanks. Here are the 2 files to compare.

Is this what you're looking for ?

In the attached, both sheets are in the same workbook.
Assumption that Point Number only appears once in column A.

Sheet1 Column A, green cells exist in Sheet2, red cells in columns B, C, D and E differ from Sheet2.
For ease of comparison have written point number sheet2 match into column G.

I suspect Rounding will be an issue with the numbers.
The macro compares the as-is values and virtually everything differs.
See comment in the code to use all numbers rounded to 7 decimal places.

NoS,

Thank you very much for creating one so fast. I finally had a chance to test it. Eventhough it did not have the option to choose between 2 files, I still can make use of the routine and copy the information I need onto the 2nd sheet before running the macro. I like the color scheme and the sheet 2 column/row reference. Nice work!