Compare spreadsheet/worksheet with changing rows

[theobromine]

Looking for a way to compare 2 data sets (in this case an excel template sent out to various users for them to fill out only certain cells and return to a single user). The data sets returned might add new rows or delete a row no longer being considered. The data sets have over 50 columns and hundreds of rows so I am using a simple example screenshot for reference. The primary key will be in this example column C (Project ID). Is there a way to do a custom conditional formatting formula that would look for the primary key in Sheet/Worksheet 1 compare to sheet/worksheet 2 find the appropriate key and then highlight changes in the specified changed columns (column D in the example)

EDIT:
Or if not conditional formatting another way to solve such as VBA, PowerQuery, etc.

 

[BenW71]

VBA would be viable, there is probably a way to use conditional formatting with a XLOOKUP (or vlookup) in the function in the conditional format for ease of future support (don't need a developer to make changes).

I'm not good with conditional formatting however so i don't know how you can have a format function that does an xlookup comparison to a set range with each value being compared to the range in the old spreadsheet. I also don't know if you'd need separate conditional formats for each of the possibilities (doesn't exist, has increase, or has decreased)...

 

[BenW71]

i guess my question here is how to use a formula in conditional formatting on a column of values that would evaluate each number in that column against the other spreadsheet.

Ie i would do the formula like =ISERROR(VLookup(B1,OtherSheet!B:B,1,FALSE)) to show the cells with a format if they don't exist on the other spreadsheet... Then a formula after that one like =IF(B1<VLookup(B1,OtherSheet!B:B,1,FALSE)) and have that be green to show the number is lower, and you could do the same with > to show the number is higher...

But how do you have that on a whole set of numbers so it evaluates with B1, B2, B3, etc across the column without creating thousands of rules (one per cell)?