Multiple options in conditional formatting

[Rickinnocal]

Hi guys, here is what I'm trying to do...

I have a cell, call it A1, that is YES/NO. The default value is NO.

If it is changed to YES, certain other cells, not otherwise needed, have to be completed. I would like to use conditional formatting to make those cells turn red if A1 is changed to YES so that the user doesn't forget to complete them. That's easy enough, I can do that no problem.

BUT, once the user does put data in each cell, I want the red to vanish. I've got other cells that are red until populated, so that's no problem either - but how to get 'both' conditions checked is messing me up. I've tried an AND statement in the rule.... ="" AND A1="YES". for example, but it doesn't seem to work.

Can anyone point me in the right direction?

Richard

 

[sparky2205]

With A1 as you Yes/No cell and C1 as your red cell
=AND($A$1="Yes",ISBLANK(C1)) applied across your range

 

[etaf]

=AND( $A1 = "YES", OR(OtherCell <> "", OtherCell1 <> "", OtherCell2 <> ""))
you may need a $ sign in front of the Column ID

Book6
	A	B	C	D	E	F	G	H	I	J
1	Y?N	Datareq1	Datareq2	Datareq3	Datareq4	Datareq5	Datareq6	Datareq7		FALSE
2	YES	1	1	1	1	1	1	1		FALSE
3										FALSE
4	Yes	1	1	1	1	1	1			FALSE
5										FALSE
6	Yes		1	1		1	1			TRUE
7										FALSE
8	yes	1	1	1	1	1	1	1		FALSE
Sheet1

Cell Formulas
Range	Formula
J1:J8	J1	=AND($A1="YES",OR($B1="",$C1="",$D1="",$E1=""))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:H	Expression	=AND($A1="YES",OR($B1="",$C1="",$D1="",$E1="",$F1="",$G1="",$H1=""))	text	NO

 

[Rickinnocal]

Thank you, the "isblank" option worked. The second answer wasn't what I wanted, it left the range red if 'any' cell was blank, I wanted it specific to each cell.

I also found I actually had it right before - Excel was editing my formula for me and making it wrong.

If I type in [ B4="YES" ] as the formula, and then apply it, it doesn't work. If I go back and click "Edit Rule", the formula now reads [ ="B4="YES"" ]. If I delete the extra quote marks, it works fine. How do I stop Excel adding the extraneous quotation marks?

 

[sparky2205]

Explicitly enter the = in your formula.
i.e. instead of B4="Yes" type =B4="Yes"