Compare two large Excel sheets for changes to data and highlight the differences

I need to track changes in two large Microsoft Excel sheets, and then highlight the differences in the second sheet. The two sheets may have different numbers of rows and may also be in different orders, but each row will have a unique identifier. As such, I chose to use vlookup, to compare rows with unique id's. However, when I use vlookup with conditional formatting, I can make it work for the first column only. When I use the Format Painter button to clone the the formatting horizontally. The Format Painter works for all of the variables, except the column number. And since there are over 100 columns, it would take forever to format each column individually. My question is two fold: Is there a trick to making the format painter work horizontally. Or is there a better way to accomplish what I'm trying to do?  Attached is a paired down version of my excel file.
compare-ee.xlsx
kmgishAsked:
Who is Participating?
 
abbas abdullaCommented:
Hi,
If the sequence of columns is same in the two sheets then, change to conditional formatting formula to be
 =(VLOOKUP($A2,Sheet1!$A:B,COLUMN(),FALSE)<>B2)

Check the attached file
compare-ee.xlsx
1
 
kmgishAuthor Commented:
Thanks, Abbas!  That worked perfectly!  Exactly what I needed.

Thanks again!

Mark
0
 
abbas abdullaCommented:
You are welcomed. Please mark question as solved if this was helpful.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.