Exact syntax for OR() in Conditional Format

[Dennis]

Excel 2000

I know the OR() statement in Excel but having a problem
when applying it to Conditional formating.

If A1 is not equal to either "low" or "medium" or "high",
I would like the A1 to turn red.

I entered Not-Equal-to: OR(A1="low",A1="medium",A1="high")
it did not work.

I need the exact syntax to type into the box before XL
changes it to what ever it wants for conditional
formatting.

Thanks so much in advance!
Dennis

 

[Peo Sjoblom]

Change the equal sign to <> (not equal)

 

[Andy B]

Hi

Try:
=AND(A1<>"low",A1<>"medium",A1<>"high")

 

[Guest]

Peo,

Thanks for your reply.

I entered OR(A1<>"low",A1<>"high",A1<>"medium")

After clicking OK

XL changed above to:

="OR(A1<>""low"",A1<>""high"",A1<>""medium"")"

It did not work. What am I doing wrong?

 

[Guest]

Thanks Andy,

Well it did not work!

I can not believe that XL does not follow its own formula
logic.

BTW I tried "Equal to" and "Not equal to" with:
=AND(A1<>"low",A1<>"medium",A1<>"high")

In short, I want A1 red if "xxx" is typed in and white
if "low" or "medium" or "high" are in A1.

Dennis

 

[Peo Sjoblom]

Select formula is as opposed to cell value
then use

=OR(A1<>""low"",A1<>""high"",A1<>""medium"")

 

[Andy B]

Well, it works for me!!

--
Andy.

Thanks Andy,

Well it did not work!

I can not believe that XL does not follow its own formula
logic.

BTW I tried "Equal to" and "Not equal to" with:
=AND(A1<>"low",A1<>"medium",A1<>"high")

In short, I want A1 red if "xxx" is typed in and white
if "low" or "medium" or "high" are in A1.

Dennis

 

[Dennis]

Thanks again, but it did NOT work!

I can not believe how counterintuitive the Conditional
Formatting setup is.

Dennis

 

[Andy B]

Ah. Penny drops!! You need to use Formula Is not Cell Value Is.
Sorry, should have spotted that earlier!

 

[Dennis]

Andy,

With all due respect, it does not work for me.

I tried AND vs. OR, then, = vs. <>

Also, I tried Cell Value vs Formula
"Equal to vs "Not equal to"

None of the variations work.

Which set of parameters did you use?

Dennis

 

[Peo Sjoblom]

Actually it is pretty intuitive, my mistake was that I looked at your
formula instead
of reading the question. The conditional formatting is triggered if the
result of the formula is
TRUE or 1, so if you want it to react you need to use AND instead the way
Andy posted
but you might want this amendment for empty cells since I believe you don't
want empty cells to be red?

=AND(A1<>"",A1<>"low",A1<>"medium",A1<>"high")

 

[Dennis]

Now it is my turn to swallow to penny!

I guess that I did not try "formula"

Your solution does work !!!

Thanks so much!!

Dennis

 

[Guest]

Interesting point(s).

Thanks for your time.

BTW, I also missed things along the way. Especially when
I accidently started to use (in my application), the A1
cell reference that I used for my question in this forum.

My actual (application) cell was G10. Now I have NO hair
on my head!

Dennis