Conditional Formatting of Cell Range based on value of 3 different Cells

[Legacy 319150]

Hi All

I have been helped before by Snakehips and Stridhan (to great effect)... so hope that this request/improvement is possible.
I have tried to explain below, but can't upload a picture or a sample sheet to further help explain... but here goes

Column C i have the total Stock i hold in a warehouse
Column D is what is ordered but not received
Column F-M is how the stock will be consumed

Working Presently i have conditional formatting in F7 that works fine (below) this makes the stock i have in of a particular part number green to the value in Column C. The remainder of the Line is RED (to denote i do not have this stock)

I'd like to further enhance this (if possible) to have the stock i hold in Green, the stock that is one order (value of column D) in Orange, and the rest of the row (for stock that i don't have) Red.

In English the "Orange" formula needs to calculate like this...

Take the Value of C7 count along F7-M7, when you have reached the total of C7 (these will be green if i keep the current formatting) colour the cells in the remaining row to the value of D7 (orange) - the rest of the row will be red

I am aware that this is not simple!. I'd be happy to colour all of the cells red by Default and get the Green (what i have) /Orange (whats on Order) to format the cell - the end result will still give me visually the 3 different couours i require.

[TABLE="width: 549"]
<tbody>[TR]
[TD="colspan: 2"]Current Conditional Formatting[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Use Formula[/TD]
[TD="colspan: 2"] =AND(F7<>"",SUM(F7:$F7)>=$C7)[/TD]
[TD][/TD]
[TD]RED[/TD]
[/TR]
[TR]
[TD]Use Formula[/TD]
[TD="colspan: 2"] =AND(F7<>"",SUM($F7:F7)<=$C7)[/TD]
[TD][/TD]
[TD]GREEN[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 1271"]
<tbody>[TR]
[TD]Part Number[/TD]
[TD]Description[/TD]
[TD]Current Stock[/TD]
[TD]Stock On Order[/TD]
[TD]Comments[/TD]
[TD]Engineer Boot Kit[/TD]
[TD]Site 1[/TD]
[TD]Site 2[/TD]
[TD]Site 3[/TD]
[TD]Site 4[/TD]
[TD]Site 5[/TD]
[TD]Site 6[/TD]
[TD]Site 7[/TD]
[/TR]
[TR]
[TD]10000006[/TD]
[TD]Product A[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]10000007[/TD]
[TD]Product B[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]10000007[/TD]
[TD]Product B[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]10000008[/TD]
[TD]Product C[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]10000009[/TD]
[TD]Product D[/TD]
[TD]77[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]10000010[/TD]
[TD]Product E[/TD]
[TD]21[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]10000011[/TD]
[TD]Product F[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]10000012[/TD]
[TD]Product G[/TD]
[TD]18[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]10000013[/TD]
[TD]Product H[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]10000014[/TD]
[TD]Product I[/TD]
[TD]18[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]10000015[/TD]
[TD]Product J[/TD]
[TD]12[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


I have tried everything i can, but to no success - its been bending my brain... i'm either missing something really obvious, or would never have seen the solution as its either impossible, or uber techie!

Any help gratefully received.

Regards, Darren

 

[RCBricker]

You could try the following for your third conditional formatting in cell f7:

=and($f7<>"",sum($f7:f7)>$c7,sum($f7:f7)<=sum($c7:$d7))

also, the conditional formatting for both the green result (should be the first in the list) and the orange result (should be the second rule in the list) should both have the "stop if true" box checked.

 

[Legacy 319150]

RCBricker..... THANK YOU, this completes exactly as i wanted - Perfect.
Thanks for the swift response.
Regards, Darren