Conditional Formatting

P

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??????
 
M

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
 
B

Bob Phillips

Try

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

--

HTH

Bob Phillips

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

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.
 
M

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?
 
P

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.
 
M

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.
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top