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]

Hi & welcome to MrExcel.
You haven't actually said what is wrong, but you need to lock the reference cell like

=(M9+N9)>$E9*(1+0.6)

 

[mikeeakins]

Hi & welcome to MrExcel.
You haven't actually said what is wrong, but you need to lock the reference cell like

=(M9+N9)>$E9*(1+0.6)

ok so,
I want to have e9 as my inventory amount, example 3500
so m9+n9 equals 1000 so its green
m9+n9+o9=2000 so still green
m9+n9+o9+p9=2900 so this total is within 60% so its yellow
m9+n9+o9+p9+q9=4000 so its red.
As E9 inventory fluctuates the green, yellow, and red cells should change to reflect the inventory.
My spreadsheet is pretty large so I will have 30 rows of data just like e9.

 

[Fluff]

Ok, how about, for green

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

 

[mikeeakins]

Ok, how about, for green

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

I think thats close .
It worked when the inventor was 3309 (e9), and green was to (O9 4/1) which added to 2960 so it was correct the rest of the way.
When I changed the inventory to 4000 it was green to (T9 4/6) but that total was 7110 and it should have been yellow at (Q9 4/3) with that total was 4260.

 

[mikeeakins]

I think thats close .
It worked when the inventor was 3309 (e9), and green was to (O9 4/1) which added to 2960 so it was correct the rest of the way.
When I changed the inventory to 4000 it was green to (T9 4/6) but that total was 7110 and it should have been yellow at (Q9 4/3) with that total was 4260.
View attachment 88653

This formula will be in every row

 

[Fluff]

Ok, how about

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

 

[mikeeakins]

So what would you use for yellow and red?
Yellow seems to be =SUM($M9:N9)>=$E9*60%

 

[Fluff]

I would have yellow should be

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

 

[mikeeakins]

and Red?