HELP! Excel Conditional Formatting!!

[jerichardson]

I use office 2003 here at work and I'm hitting a small road block right now. I'm trying to monitor weather or not our DVRs are recording all of their appropriate monitors and in every box I put one of the following codes:

OK
NV
Z
MU
MD
ML
MR

I'm now trying to make it so that if the cell is equal to "OK" than the background of the cell turns green, if it's "NV" than it turns red, and if it's any of the rest of them it turns yellow. Now I'm sure by that last line you've figured out my problem. Excel 2003 has a conditional formatting restriction of 3. SOOO ... does anyone know of a way for me to tie all of the rest of those into a single formula that I can enter into the conditional formatting window so that if the cell contains any of the following:

Z
MU
MD
ML
MR

It will turn to a yellow background.

Thanks!!

 

[SkipVought]

Hi,

FIRST, shade ALL the cells YELLOW.

THEN do the Conditional Format for OK and NV.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[jerichardson]

That seems too simple for me I was hoping that there would be some way to say IF this cell = (then list the multiple criteria) THEN apply the conditional formatting.

I think that would work fine ... but I'm a curious person and want to make it work perfect

 

[SkipVought]

Why make it more complex?

But if you insist...
[tt]
=AND(A1<>"NV",A1<>"OK")
[/tt]
You REAALLY do not want to list ALL the options in the third (yellow) category. What happens when another EXCLUDED value shows up in your data that you did not include in your OTHER list???

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[jerichardson]

I'm the only person that edits the worksheet, others view it though. Theoretically I shouldn't put anything in there but the options I have selected. Thanks for the help though!

 

[jerichardson]

I actually ended up using:

=OR(A1="MU",A1="MD",A1="ML",A1="MR",A1="Z")

That worked like a charm. Thanks for the help!

 

[SkipVought]

FYI, that approach is generally wrought with pitfalls.

But, as one surgeon said to the other, "Suture self!"

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[SkipVought]

oops

...fraught with pitfalls

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[mintjulep]

Skip's original suggestion is correct.

You really have ONLY TWO conditions to deal with.

 

[xlbo]

For reference - if you really have an urge to go with >3 conditions: thread68-223068

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[yogia]

Hi jerichardson:

As it has already been stated ... you have actually two conditions to format. However, if you want to use three condions, follwing is one way:

1) key-in
OK
NV
Z
MU
MD
ML
MR
in cells E1:E7

2) then for cell A1, use the following formulas for ConditionalFormat

Condition1: formula is ... =MATCH($A$1,E1:E7,0)=1 -- green

Condition2: formula is ... =MATCH($A$1,E1:E7,0)=2 -- red

Condition3: formula is ... =MATCH($A$1,E1:E7,0)>2 -- yellow

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[jerichardson]

You guys are crazy smart ... here's what I ended up doing. I created a rule for NV and OK by themselves. Then I used this:
=OR(A1="MU",A1="MD",A1="ML",A1="MR",A1="Z")
for the last condition. By doing that I seem to have resolved my issue. Since I only use these:
OK
NV
Z
MU
MD
ML
MR
fields it (hopefully) eliminates any possibility for me entering any incorrect information.

Thanks to all of you for help ... you guys are awesome!

 

[SkipVought]

"... possibility for me entering any incorrect information..."

There's a difference between Conditional Formatting, which... FORMATS... and Data Validation, which can prevent incorrect data from being entered.

Check out Data > Validation -- LIST where you can have a LIST of valid entries.

Bottom line. You STILL ONLY have 2 CF Conditions.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]