Conditional IF Or Format

[mjones]

Hi All,

Can I turn a cell’s contents orange

If G100>today() and G101=0
Or
If H100>today() and H101=0
Or
If I100>today() and I101=0
Or
If J100>today() and J101=0

This will help me not forget to pay my taxes when they are due - easy
to do with my crazy life.

I know how to do IF AND, but Not IF OR.

Thanks for any help,

Michele

 

[Pete_UK]

You can use conditional formatting to do this. When you bring up the
CF dialogue, choose Formula Is rather than Cell Value Is in the first
box, and put this formula in the next box:

=OR(AND(G100>TODAY(),G101=0),AND(H100>TODAY(),H101=0),AND(i100>TODAY
(),i101=0),AND(J100>TODAY(),J101=0))

Click on the Format button, then the Patterns tab and choose orange,
then OK your way out.

Hope this helps.

Pete

 

[mjones]

You can use conditional formatting to do this. When you bring up the
CF dialogue, choose Formula Is rather than Cell Value Is in the first
box, and put this formula in the next box:

=OR(AND(G100>TODAY(),G101=0),AND(H100>TODAY(),H101=0),AND(i100>TODAY
(),i101=0),AND(J100>TODAY(),J101=0))

Click on the Format button, then the Patterns tab and choose orange,
then OK your way out.

Hope this helps.

Pete

Hi,

Thanks for helping out. As is sometimes the way, when one gets what
they asked for, they don't realize that's not what they want. Looking
at it now, I forgot and element. This is what I need.

If G100>today() and G101=0 and G102<>0
Or
If H100>today() and H101=0 and H102<>0
Or
If I100>today() and I101=0 and I102<>0
Or
If J100>today() and J101=0 and J102<>0

I tried this formula, but it doesn't work:

=OR(AND(G100>TODAY(),G101=0,G102<>0),AND(H100>TODAY
(),H101=0,H102<>0),AND(I100>TODAY(),I101=0,I102<>0),AND(J100>TODAY
(),J101=0,J102<>0))

Here is the data, which should not turn the cell value orange:

g h i j
100 5-Oct-09 5-Jan-10 5-Apr-10 5-Jul-10
101 $853.42 0 0 0
102 0 $1,432.39 $65.77 0

If I put numbers in H101, I101 and J102 the cell value is not orange,
but the will never happen.

Any help would be appreciated.

Thanks again,

Michele

 

[mjones]

Why do you say that it shouldn't turn orange?

Your data will satisfy the condition
 If H100>today() and H101=0 and H102<>0
and also
 If I100>today() and I101=0 and I102<>0

in other words you satisfy
AND(H100>TODAY(),H101=0,H102<>0)
and also
AND(I100>TODAY(),I101=0,I102<>0)
--
David Biddulph





Hi,

Thanks for helping out.  As is sometimes the way, when one gets what
they asked for, they don't realize that's not what they want.  Looking
at it now, I forgot and element.  This is what I need.

If G100>today() and G101=0 and G102<>0
Or
If H100>today() and H101=0 and H102<>0
Or
If I100>today() and I101=0 and I102<>0
Or
If J100>today() and J101=0 and J102<>0

I tried this formula, but it doesn't work:

=OR(AND(G100>TODAY(),G101=0,G102<>0),AND(H100>TODAY
(),H101=0,H102<>0),AND(I100>TODAY(),I101=0,I102<>0),AND(J100>TODAY
(),J101=0,J102<>0))

Here is the data, which should not turn the cell value orange:

               g                 h                i             j
100     5-Oct-09     5-Jan-10     5-Apr-10    5-Jul-10
101     $853.42            0               0            0
102          0          $1,432.39     $65.77        0

If I put numbers in H101, I101 and J102 the cell value is not orange,
but the will never happen.

Any help would be appreciated.

Thanks again,

Michele

Ah, got it! Thanks for all your help everyone.

I'm a bit dyslexic and something you said helped. I should have said
if today()>G100 and not if G100>today().

Take care all,

Michele