Need Macro to Compare Two Sheets with Same Columns Highlight Differences using Unique ID

I know this is a relatively simple macro, and surely this has been answered before but I cannot locate it. But, Schedule Compare Tool.xlsmI'm struggling and now wasting more time then making progress.

In a nut shell, I have two sheets that need comparing (Old to Current). It first needs to find the exact match in Column A (ID). Then compare all the follow on data (Columns B-F) for that Unique ID & highlight the differences on sheet 1 (Current). If no match to Unique ID is found in either sheet, it should highlight that ID a different color.

Please help! THANK YOU SO VERY MUCH!

sounds like a simple vlookup formula???

will look at it later

Everything sounds simple...unless I have to try to do it! :-) THANK YOU!! Any help is MUCH appreciated.

Hi,

I don't think you need a macro to do this... if I understand what you want you should be able to do this with 2 conditional formatting statements and a few vlookup & match functions.

Take a look at the attached and see if this is what you're looking for...

Best regards,
Mike

I suggested the macro because I will use this over and over, daily with new data blown in to make updates from (same fields/columns, just new info in those fields). This does appear to work (YOU"RE AWESOME!), I just worry I'll break the formula when inputting (usually copy/paste) the new data. Thoughts?

oh ok, in that case what you want to do is just expand the range for both the vlookup functions and the conditional formatting "applies to" range to something that will cover the amount of data you would put in... pick something like 10,000 rows and make sure the formulas in columns H to K go down that far.

Hope that helps!

Ok, I'll give that a try. THANKS!!!!

It loses the conditional formatting when a new set of data is pasted in...or I broke the formula when I changed the range. Where's the highlight function stored? In the formula or the cells, so if I paste overtop, am I clearing that Cond. Formatting?

Ok here take a look at this. i've made a place for you to paste the data you have...up to 1000 rows. The other sheets read off of that. Take a look at the formula's in the conditional formating>manage rules... choose this worksheet, and you'll see how the formulas in here are basically like an if statement that control the formatting. Pay close attention to the hierarchy of the 3 conditional formatting formulas.

Play around with it and let me know if you have any questions.

br,
Mike

THANK YOU SO MUCH!! I'll Try it out and dump some new data in it tomorrow as well! I'll let you know.