Conditional Formatting

[Pat999]

I want to color a cell based on the value of that cell being greater than
zero and another cell not being equal to "x".

I've used conditional formatting with "formula is" and formula -
IF(A1>0,B1<>"X")

This does not seem to work - what am I doing wrong??????

 

[Max]

Use AND for multiple criteria to be satisfied, like this ..
Assume you want to apply CF to col A
Select col A, apply CF using Formula is: =AND(A1>0,B1<>"X")
Format as desired>OK out
Success? hit the YES below

 

[Bob Phillips]

Try

=AND(A1>0,B1<>"X")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Pat999]

Tried that - no luck - my formula field in conditional formatting for D8 now
reads ="AND(D8>0,E8<>""X"")" and the format is to pattern color the cell

D8 contains a value greater than zero, E8 is blank, but the cell D8 is not
colored.

 

[Max]

It should have worked fine
a. Did you use "Formula Is" option in the CF?
b. Look closely at your adapted CF formula:
="AND(D8>0,E8<>""X"")"
It is NOT what I posted. There's additional double quotes (") all over the
place. Go back into your CF "formula is" box, manually amend the above so
that it reads as:
=AND(D8>0,E8<>"X")
Once again, I assure you that it should work fine for you.
Hit the right button, won't you?

 

[Pat999]

The extra (") wre not keyed by me - they seem to be added after I closed the
formatting window.

However, I manually removed them and it works fine - thanks.

 

[Max]

But I didn't exactly say that you did it, if you read it again carefully (I'm
pretty careful in all my responses). Blame Excel for it, if there's a need to
ventilate. Anyway with the experience you're much better off now, knowing
firstly, how to frame up the CF formula for multiple criteria, and secondly,
what could possibly go wrong when you apply the CF and how to check/correct
it so that it works properly.

p/s: I don't post responses which don't work. It would have passed QC here.

 

[David Biddulph]

I think you'll find that the reason the quote marks were added was that you
forgot to start your formula input with an = sign. Excel takes your input
as being a text string adds the = and quote marks.