Comparing 2 Rows By Looping Through Cells

I've uploaded my code with an example database.

The macro is supposed to generate a report on what has changed between the "Current" and "Previous" sheets.

The first step is to compare Current to Previous by the reference column and determine if there were any new records added. If so those records are copied to the "Report Comparison" sheet and highlighted yellow. This seems to be working.

The second step is to compare Previous to Current and determine if there were any records that were deleted. If so those are copied to "Report Comparison" and highlighted green. This seems to be working also.

Next the macro is to compare each record cell by cell, and if there is a particular cell that has been changed in a record, the entire row from Current is copied to "Report Comparison", but only the cell that changed is highlighted yellow and a comment is added to that cell showing what the previous value was( adding the comment has not been attempted in the code yet).

The last section of the code is commented out for now, but I had started by looping the cells in one specific row to see if the code was correct before trying to also loop through all the rows. If I include this code the results aren't correct. All the rows from Current and Previous are getting copied to the report. I'm not sure where the code is wrong.

Any help is appreciated. Thanks

AlphaFrog,

Thanks for the help. Your code is a lot more efficient than mine. I'm getting a run time error, I've attached a screenshot.

Originally Posted by AlphaFrog

Try this...

Please Login or Register  to view this content.

Originally Posted by AlphaFrog

What's the error description?
Did you test it with your "DBCompare - Example.xlsm" workbook.?

I'm getting "Run Time Error 1004 Application-defined or object-defined error"

I copied your code into the same workbook I uploaded and created a new macro DatabaseCompare2.

Originally Posted by AlphaFrog

What's the error description?
Did you test it with your "DBCompare - Example.xlsm" workbook.?

I've attached the workbook with the results of the error. The last cell in the last row is where the error is, the code is attempting to add a comment.

Originally Posted by AlphaFrog

I got it to error.

Change this...
Cells(NextRow, c).AddComment.Text wsPrev.Cells(v, c).Text 'show change as comment

Yep that worked. I really appreciate it.

Originally Posted by AlphaFrog

Try this...

Please Login or Register  to view this content.

AlphaFrog,

I took your code and tried to replace specific row/column numbers to variables since this macro can be used with a variety of databases. I'm a bit stuck at this point where if I run the macro, it acts like it doesn't know which sheet to copy the data to, so the "Current" sheet gets messed up. I've attached the workbook with the aftermath of "Current". I made a backup of the "Current" sheet in order to test the macro again.

Thanks

Works like a charm. Thanks again.