Conditional Formatting w/ Formulas

[Austin Lang]

Hey all,

I feel like this is a simple one but I am having a brain fart about it..

I have a document that has these separate conditional formatting rules.. How do I go about combining these into one? I attempted to add OR but I am falling short somewhere.

=AND($A5="Circuit 1",$AB5<1.5)
=AND($A5="Circuit 2",$AB5<1.5)
=AND($A5="Circuit 3",$AB5<1.5)

Thanks in advance.

 

[bebo021999]

may be:

=AND($AB5<1.5,OR($A5={"Circuit 1","Circuit 2","Circuit 3"}))

 

[kevin9999]

@bebo021999 just beat me, but here's the same (just reversed) with a demo:

CF.xlsx
	A	B																									AA	AB
4
5	Circuit 0	test																										1
6	Circuit 1	test																										1
7	Circuit 2	test																										1.25
8	Circuit 3	test																										2
9	Circuit 4	test																										2
10
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:B9	Expression	=AND(OR($A5="Circuit 1",$A5="Circuit 2",$A5="Circuit 3"),$AB5<1.5)	text	NO

 

[Austin Lang]

Thank you both for your answers! I now seem to have a conflict with another rule. Originally, I had to these different rules..

=Cell Value >0.4

=AND($A5="Circuit 1",$AB5<1.5)
=AND($A5="Circuit 2",$AB5<1.5)
=AND($A5="Circuit 3",$AB5<1.5)

Now that we have combined the three, now it is conflicting with the first formula. It was not conflicting previously. The end goal would be to follow the first rule unless it is Circuit 1, 2, or 3.

 

[kevin9999]

Is this what you mean?

CF.xlsx
	A	B																									AA	AB
4
5	Circuit 0	0.3																										1
6	Circuit 1	0.4																										1
7	Circuit 2	0.5																										1.25
8	Circuit 3	0.6																										2
9	Circuit 4	0.7																										2
10
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:B9	Expression	=AND(OR($A5="Circuit 1",$A5="Circuit 2",$A5="Circuit 3"),$AB5<1.5,B5>0.4)	text	NO

 

[Austin Lang]

I am looking for a rule (or two) that says that the first one should be red because it doesn't meet the criteria, but the 2nd line should be green.

 

[kevin9999]

I'm starting to lose your flow of logic now, but perhaps this:

CF.xlsx
	A	B																									AA	AB
5
6	Circuit 1	8																										1
7	Circuit 6	8																										1.25
8
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B6:B7	Expression	=AND(OR($A6="Circuit 1",$A6="Circuit 2",$A6="Circuit 3"),$AB6<1.5,B6>0.4)	text	NO

Where the cells are green to start with, and the conditional formatting turns it to red.

 

[Austin Lang]

I realized after I replied last, I have made a mess of it, so I apologize for that. Let me try and clear it up..

By default, the cell is red. It is red until proven it can be green, if that makes sense. I we have approx. 50 circuits that can be entered. If it is Circuit 4-50, I want it to follow the rule that the value entered into V needs to be greater than 4 to be green. If it is Circuit 1-3, I want it to only turn green if the number is greater than 15. (Ignore column W, that one is not formatted correctly. I will fix that after I get this one done.

 

[bebo021999]

Try again for green:

=$V5>IF(OR($A5={"Circuit 1","Circuit 2","Circuit 3"}),15,4)

 

[Austin Lang]

Try again for green:

=$V5>IF(OR($A5={"Circuit 1","Circuit 2","Circuit 3"}),15,4)