Excel – Multiple Conditional formatting to groups of cells within same sheet

Topic

New Reply

Hello! I have a “calendar” of inventory & I’m trying to apply conditional formatting to show if it is not here (pink), here (green) or installed (no color). My formula should show that if:

IF column name “LEFT” >0 then “Item” through “Left” should be pink

IF column name “HERE” =blank or >0 then “Item” through “Left” should be green

IF column name “LEFT” =0 then “Item” through “Left” should be no fill/white

I’ve tried a few things but all it does is color the entire row, not just the “Item” through “Left” columns.

44183-ScreenShot003

44184-COND-FORM

Reply | Quote

Replies

What happens if there is a conflict? Left > 0 and Here > 0 … for example.

Is the order left > 0, then here > 0 or blank, then left = 0?

Reply | Quote

What happens if there is a conflict? Left > 0 and Here > 0 … for example.

Is the order left > 0, then here > 0 or blank, then left = 0?

Good question.

Yes, the order is left > 0, then here > 0 or blank, then left = 0

To give it context, I get a contract in to install, say, 9 items. Therefore, TOTAL is 9 & LEFT is, by default, 9 (row from “Item” to “Left” is pink at this point). Then 9 items are delivered for install so all 3 columns will say 9. As they get installed, the LEFT column will slowly dwindle down to 0 & turn green.

Reply | Quote

Post #1 IF column name “LEFT” =0 then “Item” through “Left” should be no fill/white

Post #3: As they get installed, the LEFT column will slowly dwindle down to 0 & turn green.

wsammy,

your context description in post #3 does not match your images nor description in post #1

Reply | Quote

Sammy,

You also do not specify what color should be used if you only get a partial delivery, e.g. 9 ordered, 4 delivered. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi

With apologies to Yogi Berra, Excel is 90 percent mental. The other half is physical.
So here’s my physical solution:

1. Use a formula in the “LEFT” column (this means you only need to enter the Item Total due, and what is “HERE”
2. Conditional formats have been applied separately to each Week column group.
3. If you get less than was expected, the record remains pink. (i.e. short delivery)
4. If you get exactly what was expected, the record is pale green
5. If you get more than was expected, the record is darker green.

(The attached file is .xls XL2003 format)

zeddy

Reply | Quote

Hi

With apologies to Yogi Berra, Excel is 90 percent mental. The other half is physical.
So here’s my physical solution:

1. Use a formula in the “LEFT” column (this means you only need to enter the Item Total due, and what is “HERE”
2. Conditional formats have been applied separately to each Week column group.
3. If you get less than was expected, the record remains pink. (i.e. short delivery)
4. If you get exactly what was expected, the record is pale green
5. If you get more than was expected, the record is darker green.

(The attached file is .xls XL2003 format)

zeddy

THANK YOU. I have a program at work that I accurately do this in. This excel calendar thing is only used for a quick reference in meetings & obviously is not 100% accurate. You have made my life a lot easier. Thank you!!!!!!

Reply | Quote

Hi Sammy

Thanks for the feedback.
Always good to know our efforts are appreciated. It makes a difference!

zeddy

Reply | Quote

Zeddy – I see you came to a fork in the road and took it!

My favorite Yogiism is about him going into a pizza shop and ordering a whole pie. The waiter asks should the pie be cut into 6 slices or 8 slices. Yogi asks that the pie be cut into 8 slices because he’s really hungry. Something to remember the next time you’re with friends ordering a pie.

Reply | Quote

Hi Fred

..I watched a baseball game once.
I couldn’t understand why the ball was getting bigger and bigger and bigger.
Then it hit me.

zeddy

Reply | Quote

Hi Fred

..next time I have a pizza for myself, I’ll just cut it in two pieces then.
Mustn’t appear greedy.

zeddy

Reply | Quote