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

[skylark332]

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.