Color all cells with values that are conditional on quantity column

Hi folks.
I'm really struggling on how to go about getting this last piece of form implemented.

Essentially, on one page I have Products, their quantities and substitute products. I need to know when a substitute is not in stock by coloring it green or red.

Example:
PN0003 is a substitute for PN0001. If PN0003 has a quantity of 0 (as shown on the same or a different sheet), then all instances of PN0003 needs to be coloured Red. If it is >1 it needs to be green.


I've tried doing a combination of vlookups, conditional formatting, matches but nothing seems to hit the spot. Ideally I need a way for a cell (In F through Z) to match it's value with what's in Column A, and if the QTY of the part (which is in Column D), is 0 = Red, >1 = Green, then color the F-Z cell appropriately.

I also can't go through and do this on a cell by cell basis as there is going to be hundreds of listing and they may change order

I've attached a snippet of the file in it's original form on Sheet 1, and Sheet 2 show's what I ideally need it to turn out like.

Any assistance is appreciated.

Exchange-Example.xlsx