google sheets – Inventory Management where main list highlights items checked out/ in

I am working on an inventory management list where barcodes are scanned out and scanned back in. My sheet is attached to forms. I have set the item sku# on the main inventory list to highlight red when checked out and pull the location of the item from the checked out list over to the inventory sheet (column L), but I am struggling to make it show when an item is checked back in. Ideally I would like the item sku# to highlight red as shown when it is checked out and have the location show in yellow when checked out, but I need the item sku# to go back to no color when checked in and remove the item location from the main inventory list when checked in. I attempted using a formula I found on the status tab, but it is not showing when items are scanned multiple times. Is there a way to code this so that it does it with each scan in/out? I have tried using several formulas, but I am not all that great with excel. any help is greatlyenter link description here appreciated.