Conditional formats in Excel - using IF to get same format for 2 diff

[LizF]

Hi folks,

Hopefully this will be an easy one for you all, as I'm sure I've seen something about this when browsing the forum. However I can't for the life of me find the same thread, so here goes...

I'm using conditional formats in Excel to alter a cell's background colour (Red, Amber or Green). However there are 2 conditions which I want to produce a Red background

i.e. =Q8=3 --> Red

=Q1>0 --> Red

This is an "OR" type realtionship so I think I need an

if(Q8=3, *turn red*, IF(Q1>0),*turn red*,*don't turn red*)

type of thing but I don't know the syntax.....

Thanks in advance...
Liz

 

[SkipVought]

Hi,
Use Formula IS

=OR(Q1=0,Q8=3)

Hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[combo]

When you are in conditional formatting, select "formula is" and write formula: =OR(Q8=3,Q1>0) and select "format" when cells aatisfy this condition

combo

 

[LizF]

Thanks guys, that makes a lot of sense. However....I can't get it to work!! I've pasted the "Formula is"... formula I've entered below...can you see what I've done wrong?

=OR($O$12=3,$O$1>0)

Thanks,
Liz

 

[combo]

Hi Liz,
To check:

1. write condition in small letters, ie =or($o$12=3,$o$1>0) and accept. When you oprn again conditional format dialog, all should be in capitals. If not - check if there are no leading or trailing spaces in formula.

2. check if you defined the cell format when 01>0 or O12=3 (on conditional format dialog, the right button, select font colour, border and/or shading).

3. Put 3 in cell O12 and say 1 in O1. If your conditionally formatted cell contains text or formula, see what happens. If it is empty, conditional formatting may not work as excel does not recalculate this cell.

Good luck!
combo

 

[KenWright]

Or you could just put another conditional format in using the 'Add' button on the Conditional format dialog box, so for example you would have two conditions, ecah resulting in your desired format if true. You can have a max of 3 conditions, or 4 if you count the default state.

Regards
Ken.....

 

[LizF]

Hello again guys...

Combo, thanks for the suggestions, I went through them all, however the problem remains. I thought it was to do with the > rather than anything else - so I tried a workaround which was to have a hidden "binary" cell P1 which outputs 0 if MyCell = 0, and 1 if MyCell > 0. Then I made the conditional format

IS FORMULA =OR($O$12=3,$P$1=1) where $P$1 is the binary cell

BUT....it still won't work!! The O12=3 part works fine, it just doesn't like having the 2nd condition. I've swapped them around - no luck, it's still the P1=1 part it doesn't like.

Ken, unfortunately I already have 3 set up and I understand that no-one has yet found a way to force Excel to accept more without using VBA (which I may yet resort to!!)

BUT, your idea of using 3 conditions plus a default solves the problem - i.e. I've defaulted it to green, with 2 conditions which make it red, and one for amber. So thanks very much for that.

I would still love to understand why it won't let me use this OR statement though - if anyone has any ideas I'd be very interested.

Liz

 

[KenWright]

It should do - I just tried it with =OR($A$1=1,$A$4=3) and it works fine for me.

Format / CF / Changing 'cell value is' to 'formula is' and putting formula in box.

Tested in both Xl 2000 and 2002

Obviously one workaround (Though you shouldn't need it and it would bug the life out of me too if the above was not working for some reason), would be to have a trigger cell somewhere else on the sheet with an OR statement in it that returned say a 1, and then tie your cell to be CF'd to that instead - Would bug me though.

Regards
Ken.............

 

[LizF]

Hi Ken,

Yup, having to use workarounds is bugging me!! Still can't fathom this one out...but it occurs to me that I'm using Excel 97 - did 97 support this kind of stuff??

Cheers,
Liz

 

[combo]

Hi Liz,
It is not perfect, but you can slightly rearrange your workbook:
in cell P1 use formula: =IF(MyCell>0,TRUE,FALSE) (BTW, what's going on when MyCell<0? - I put here FALSE)
in conditional format: =OR($O$12=3,$P$1)
You will deal with explicit booleans this way, this may help.

It is strange that your formula does not work, I think the best way would be to examin the workbook.

combo

 

[xlbo]

Liz - xl97:
=OR($O$12=3,$O$1>0)
works no problems - I may be waaaaay off the mark here but initially, you were testing conditions in Q, then O - could it be as simple as referencing the wrong column ?? Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[LizF]

Hi combo

I tried your suggestion - unfortunately it doesn't seem to make a difference. Excel happily accepts any of the versions (i.e. >0, =0, =true) we've tried so far I use separate conditions, but not if I try to combine them using &quot;OR&quot;

MyCell is the result of a count, so it will either be zero or more-than-zero. Basically the count goes down a &quot;complaints&quot; column of about 450 projects and if it finds an entry from the last 35 days (entries are dates) it adds 1 to the count. This number is then used as a driver for one of the measures on a &quot;results scorecard&quot; - it turns red if a complaint has been received in the last 35 days. i.e. if the count is greater than zero.

Oh well....!!

Thanks for your help,
Liz

 

[LizF]

HI Geoff,

Sadly that is not the problem (I wish!)

I'm beginning to think there's just a perculiarity in my file - it's a humungous one with tons of code and weird things have been known to happen with big files!

I've just tried a few things out with a brand new file and they work fine...

Ah well, thanks for the help guys - looks like I've just got a glitch I've got to work around!

Liz

 

[LizF]

AAARRRGGGHHH...I've just realised what hte problem is. I'm so stoopid :-(

XL evaluates the conditions in order, so the problem was that condition 1 was over-riding the second part of the OR() in condition 3. I have now swapped conditions 1 and 3 around and it works fine.

Sorry to have caused so much expense of energy over such a daft thing!

Liz