Link to home
Start Free TrialLog in
Avatar of Sue Taylor
Sue TaylorFlag for United States of America

asked on

Conditional Format Excel 2010

I'm trying to have a conditional format highlight two cells if one of the two are empty.

In cell B14 I can enter a price per pound
In cell B15 I can enter a price per foot

I have to use either B14 or B15 for calculations in other cells, but I don't need both of them.

 I want both cells to be highlighted if the user left one of them blank.
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Select B14:B15.

Create a formula-based CF rule using the foirmula:

For at least one blank...

=OR($B$14="",$B$15="")

For exactly one blank...

=COUNTA($B$14:$B$15)=1
Avatar of Sue Taylor

ASKER

I don't get it.  Do I create both of these rules?

I tried creating =OR($B$14="",$B$15="") and it highlighted both cells but when I entered data into one of theme it didn't un-highlight them.  So then I tried the other formula and it din't highlight anything.  So then I created two CF.
In your question, you wrote:

>> I want both cells to be highlighted if the user left one of them blank.

If you use that  =OR($B$14="",$B$15="")  rule then, as I indicated above, as long as at least one cell is blank, the test is true, and whatever formatting you selected will be applied.

If that is not what you wanted, then please try another attempt at saying what you need.
That is what I want.  But it's not displaying the results as you described.  

I highlighted both cells and copy and pasted =OR($B$14="",$B$15="") into the CF and said to make the cell RED.  Both cells automatically changed to red.  But when I enter a number into cell B14 both cells remain RED.  The same thing if I enter a number into B15.

See attached.
Quote-Template.xlsx
>>But it's not displaying the results as you described.  

Of course it is.  Please re-read what I wrote:

For at least one blank...

=OR($B$14="",$B$15="")

"At least one blank" means exactly that: at least one of the cells B14:B15, and possibly both, are blank.

My CF guidance is doing exactly what you said you wanted in your question, and what you also said you wanted in http:#a38163561.  If you want something different, then you're going to have to tell me what that is.
I'm stupid.  You're right, I've explained this wrong.  The users can get the price of material by either entering the price per foot or the price per pound.  I want the cells to highlight if they fail to enter in one OR the other.  So, I want it to be highlighted if both cells are blank but NOT highlighted if either one of them have data in it.
OK, now we're getting somewhere :)

And no, you're not stupid, and I am sorry for getting frustrated myself.

Back in a jif.
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Perfect!   Thanks so much!
You're most welcome