Comparing data with IF function

Hi All,

I have another question. We get a download of trades daily, some are new trades and some old trades amended today, all gets reported to regulators.

basically I wanted to compare 2 sets of data, data in SHEET1 is the latest and data in SHEET2 are old has multiple versions on each trade. data in each row refers to single trade and can have multiple rows but are in different versions stated in column D

SHEET1 (latest)
SHEET2 (old)

Not sure how to code this but here's what I want to achieve

1. check if trade refs in SHEET1 (in column Z) is in SHEET2 (in column B). if it's not there then keep that row in SHEET1
2. if that reference is in SHEET2 then look for latest version (in numeric)row for that reference which is in column D and then compare following rows that can be in number, letters or mix
3. if there is no difference then delete that row with that trade reference in SHEET1 otherwise keep and highlight cells with difference but only in SHEET1. note we are not amending/deleting anything in SHEET2 that can have multiple rows (versions) for same trade. SHEET1 will have single row (just the latest version) for each trade.

compare COLUMNS
SHEET1 SHEET2
row H row J
row I row K
row M row O
row p row p
row Q row S
row W row Y
row X row Z


I'm not sure if above is clear, if you're not then feel free to call me on 07710056521 or email me nasira254@gmail.com

if you can you help me do this in LOOP and IF coding then I can apply this to few other projects I must do to save hours of work.

Many thanks
Nasir