Conditional Formatting Help

[M3rlin]

Hi All,
I need some help to create a conditional format that when applied to each cell in column B covers all conditions shown below (in columns C & D as well)as I have manually illustrated (green, yellow, red cell shading). The conditional format in each cell would only be conditional for the information from the same row in columns B, C, & D. I am thinking that a conditional formula can be used, although I can not figure it out. Thanks!

Staffing Tracker - BaltWash.xls
	B	C	D	E
1	Location	Potential Need	# Back Ups
2	1	0	1
3	0	0	1
4	1	1	0
5	1	1	1
6	0	1	0
7	0	0	0
Sheet

 

[Norie]

What are the actual conditions?

 

[M3rlin]

#1: Green if B1=1, C1=0, D1=1
#2: Yellow if B1=0, C1=0, D1=1
#3: Yellow if B1=1, C1=1, D1=0
#4: Yellow if B1=1, C1=1, D1=1
#5: Red if B1=0, C1=1, D1=0
#6: Red if B1=0, C1=0, D1=0

I have all of these conditions displayed in my html post. Thanks

 

[onlyadrafter]

Hello,

Enter this for the first cell and paste the formats to other cells.

Condition 1

Formula is =AND($A2=1,$B2=0,$C2=1) (FORMAT AS GREEN)

Condition 2

Formula is =OR(AND($A2=0,$B2=0),AND($A2=1,$B2=1)), (FORMAT AS YELLOW)

Condition 3

Formula is =AND($A2=0,$C2=0) (FORMAT AS RED)

I hope I've copied those correctly!

Just out o curiosity what happens if the cells contain

0 1 1 and
1 0 0

 

[M3rlin]

Thanks for the help! I had to move the 3rd condition (Red shading) to number 2. After I did that, the formulas were perfect!

To answer your question, there will never be a 0, 1, 0 for the data I am gathering and if there is a 1, 0, 0 then there is a conditional format to shade the backup column red.

Thanks again for your help!

 

[onlyadrafter]

Thats OK.

I actually have got the RED condition as the 2nd one, I thought i was being clever by putting them in the same order as your sample data.

That will teach me to check it!!