I have been stuck for months

[mikeeakins]

Hello! I asked this question and almost worked so I believe a little more tweaking is gonna be involved. My question was ... I need to conditional format a series of cells (M9:AK9) the cell that I am comparing to is (E9) This was my result for formatting below the example shown below the square it should be formatted (green, green, yellow). One this I forgot to mention was that as I increase or decrease cell E9, the color format should change also to update the formatted row. As E9 changes the green, yellow and red formatting should change in the row. I have 50 rows of data that I will be using this on in the same spread sheet. 1. Select the cells M9:AK9. 2. Go to the Home tab and click on Conditional Formatting. 3. Select "New Rule" and then choose "Use a formula to determine which cells to format." 4. In the "Format values where this formula is true" field, enter the following formula for green fill: =(M9+N9)>E9*(1+0.6) 5. Click on the Format button and choose the fill color you want for the green cells. 6. Click OK to close the Format Cells dialog box. 7. Click OK to apply the formatting rule. 8. Repeat steps 3-7 for yellow fill. In the "Format values where this formula is true" field, enter the following formula: =(M9+N9+O9)>E9*(1+0.6) 9. Click on the Format button and choose the fill color you want for the yellow cells. 10. Click OK to close the Format Cells dialog box. 11. Click OK to apply the formatting rule. 12. Repeat steps 3-7 for red fill. In the "Format values where this formula is true" field, enter the following formula: =OR(SUM(M9:N9)>E9,SUM(M9:O9)>E9*1.6) 13. Click on the Format button and choose the fill color you want for the red cells. 14. Click OK to close the Format Cells dialog box. 15. Click OK to apply the formatting rule. Now, the cells M9:AK9 will be formatted with green, yellow, and red colors based on the criteria you provided. Kindly let me know. I appreciate any help I can get for this problem. Thanks, Mike

 

[Fluff]

Just change the < to >=

 

[mikeeakins]

SO this is where I am at

and this is what I get
I should never have more than 1 yellow formatted square

 

[Fluff]

Then what exactly are the 3 criteria?

 

[mikeeakins]

Criteria for green is What cells are (= or less than or greater than) inventory amount.
Starting in the (((1st row 550,1350,870 =2770 so those 3 cells should be green) and +1080 =3850 so its within 60% so it should be yellow) and + 630= 4480 so it should be red) and the rest of the way to 1300 should also be red..

 

[Fluff]

That does not tell me what the criteria are.

 

[mikeeakins]

Maybe I am not explaining it correctly.
I need all cells < what the inventory (4305) to be green example ( A5+A6= 1900 is greater then 4305 then Green,) (A5+a6+a7 = 2770 then all 3 cells green,) (Then a5+a6+a7+a8= 60% of 4305 then Yellow, ) ( a5+a6+a7+a8+a9 > then 4305 the red) so the rest of the remaining cells are to be red. DId this help?
I need any cell that is within 60% of the inventory to be yellow.
I need all cells greater then the inventory to be red.

 

[Fluff]

Maybe for yellow

=SUM($M9:N9)<=$E9

and red

=SUM($M9:N9)>$E9