Macro to compare check-in vs. check-out data

Ok so i'm using an excel table to manage a deployment of some equipment, the person picking up equipment will have a badge with QR code (unique identifier) and the equipment will have the same. I currently have an excel workbook with 2 tabs one for picked up data and one for return data.

I want to see if there is a way to run a macro to compare the 2 lists at the end of the day to ensure not only all equipment was returned but each person returned the equipment they originally picked up. Ideally i would like the data returned on a 3 tab that would highlight in green all those that are correct, highlight in yellow discrepancy, highlight in red data that exists on one sheet but not the other with a reference to which tab the original data is found on. I have attached a picture of what each tab looks like in my workbook.

I know this could be done with a Vlookup and concatenation but i'm looking for a less manual option and not sure if this is even possible. Any help would be greatly appreciated.

My philosophy is to use Excel to do as much heavy lifting as possible and use VBA to stitch the pieces together.

I did a couple of things with your tables. First, tables know how big they are. So you do not have to select all rows for the tables. The table will add a row each time data is entered into the next blank row. I also renamed the tables to Table_In and Table_Out and eliminated some blank spaces in the headers.

I also saw your attempts to return the cursor to the first cell of the next row. I eliminated the old code that was trying to do that and coded change events on the input sheets to return the cursor to the first column next row whenever something in column B is changed.

I did add helper columns to the tables. These are used in the code and the columns can be hidden. Tables also automatically add formulas each time a row is added.

I made use of pivot tables to give me unique values for combined data. I overlaid these with named dynamic ranges so I could search them in the code.

I can handle 4 cases:
1. A good return - a person hands the same headset that was checked out.
2. Missing return - the headset is not returned.
3. Mismatched headset - a person checks out one headset, but returns a different one.
4. Additional return - a person hands in a headset that was not checked out.

The pivot data and pivot good - bad sheets can be hidden.

I figured out what happened. If a pivot table doesn't find what it is filtering for, it reverts to (All). In this case, we have no mismatches, so the filter on the table for False reverts to all. To compensate for this I do a count on true and false and store these on the Pivot Good Bad Sheet. Depending on the results I either skip the section or reset the filter and process the data.

It was a good drill for me too. That was a good catch on the pivot tables and I need to keep that kind of error trapping in mind when I code in the future.