Conditional formatting question for Excel 2010

[asad]

Hello Guys,

My computer has recently been upgraded and now I have got Office 2010. I was happy until I came across a simple problem for you guys but looks like a big one for me.

I have inserted some conditional formatting in a range and it was all working fine untill I inserted one more condition. I definitely am inputing it in a wrong way, but can't figure out what's the right formula.
I am trying to insert a formula in conditional formatting and the formual is 6th out of 7 in conditional formatting window. The formual goes like this:

=or(I77=1,139,142,143,145,749)

and the pattern is cell colour to orange.
But when apply this condition and copy it to a range, all the other cell in the range change to orange colour. Why? They don't have any of the values mentioned in the formula, then why do the cells colour change to orange for them?

Any help would be greatly appreciated.

Asad

 

[asad]

Don't worry guys, I sorted it out. Thanks for having a look at it though.

Asad

 

[T. Valko]

Hello Guys,

My computer has recently been upgraded and now I have got Office 2010. I was happy until I came across a simple problem for you guys but looks like a big one for me.

I have inserted some conditional formatting in a range and it was all working fine untill I inserted one more condition. I definitely am inputing it in a wrong way, but can't figure out what's the right formula.
I am trying to insert a formula in conditional formatting and the formual is 6th out of 7 in conditional formatting window. The formual goes like this:
=or(I77=1,139,142,143,145,749) and the pattern is cell colour to orange.
But when apply this condition and copy it to a range, all the other cell in the range change to orange colour. Why? They don't have any of the values mentioned in the formula, then why do the cells colour change to orange for them?

Any help would be greatly appreciated.

Asad

Don't worry guys, I sorted it out. Thanks for having a look at it though.

Asad

For the benefit of others that might be interested...

It's because the formula:

=OR(I77=1,139,142,143,145,749)

Will ALWAYS evaluate to TRUE.

The logical test: I77=1, will return TRUE or FALSE. Each of the other arguments will evaluate as TRUE. Any number other than 0 will evaluate as TRUE.

You can test this behavior on the worksheet.

Enter these formulas in some cells:

=OR(0)
=OR(1)
=OR(1,0)
=OR(0.000000001)
=OR(-1,0)

So, you'd want to write the connditional formatting formula like this:

=OR(I77=1,I77=139,I77=142,I77=143,I77=145,I77=749)

 

[asad]

Thanks Biff,

That's exactly what I did and it worked perfect. Thanks again.

Asad

 

[T. Valko]

Thanks Biff,

That's exactly what I did and it worked perfect. Thanks again.

Asad

Good deal. Thanks for the feedback!