Conditional Formatting Help! Cell color dependent on other cell values.

[d_whys]

Hello all,

I am trying to set conditional formatting so that it is dependent on another cell. I want the color to change based on the percentage of what I need.

CONTEXT
I am creating an inventory forecasting sheet for apparel needs for a customer base. For example, I know for a fact that I have 1000 customers this month, and I also know that 18% of them are size Small.

On the attached sample I have 25 units of Small, but my monthly need is 180. My deficit is 155. I have only 13.9% of what I need.

I want my Surplus/Deficit column to change color based on the percentage of Monthly Needs. So the color of E7 would be dependent on D7.

For example:
Between values 0 and 0.25*D7, E7 will be red.
Between values 0.25*D7 and 0.50*D7, E7 will be green.
Between values 0.50*D7 and D7, E7 will be blue.

OR

If F7 is between 0-25%, E7 will be red
etc.

I feel like there is a pretty easy custom formula but haven't been able to find the correct one.

Any pointers or links would be greatly appreciated!