VBA to compare data in 2 sheets, issues with existing code

Hi All,

Found some code from an old post (http://www.excelforum.com/excel-prog...ml#post3701361) which compares data in sheet1 & sheet2 and highlights differences in both sheets.

Applied below code to my sample file (attached) but picked up some limitations which I was hoping someone could help me with:
- Code seems to stop comparing any further once it reaches a blank cell
- Compares dates and values relatively well but not text columns like people's names, department names, etc.
- There also seems to be some inconsistency in comparing which i can't quite figure out

Any help would be greatly appreciated.

Please Login or Register  to view this content.

Try this...

Please Login or Register  to view this content.

You're welcome.

In the VBA Editor, highlight the UBound term and press the F1 key for a description.

The Scripting.Dictionary doesn't have VBA help. It's a special array that has a very fast method to compare Records. It's used in this case to store the 1st sheet ID's (Refs) along with their row numbers (i). Then the 2nd sheet's ID's are compared to the Dictionary to find matches. Matched rows (i and k) are then compared cell by cell (j = columns).

Please Login or Register  to view this content.