IF Statement Help

[Geoff Robinson]

IF a date is before todays date and b feild is <1 then i
want to change the colour of that cell to red and if the
date is before todays date and b feild is >0 to change the
colour of the cell to green and if the date is after today
then the cell goes green

not the text, text stays black

Cheers

Geoff Robinson

 

[Jason Morin]

Format > Conditional Formatting > Formula Is...

=AND($A$1<TODAY(),$B$1<1)

and press Format, Pattern Tab, and select red. Go back,
click Add, Formula Is...:

=OR($A$1>TODAY(),AND($A$1<TODAY(),$B$1>0))

and select a pattern color of green. Note that when the B1
is between 0 and 1 exclusive and A1 < today's date, then
it will honor the first condition.

HTH
Jason
Atlanta, GA

 

[Geoff Robinson]

yes but using this does not work as i have just tried it,
but i might not have made myself clear enough.

I want a whole collum to do this and it will take forever
to go through each feild to get the conditional format
done.

or is there another way where i can set one or two
formula's up and it will do every row that i have selected

Thanks

Geoff Robinson

 

[Andy B]

Geoff

Select the whole column. Go to Conditional Formatting and enter the formulas
you were given, but do not input the $ signs. This means that the references
are not fixed (absolute), but will change down the column (relative).

Andy.

 

[Geoff Robinson]

I have already tried this, and i have just tried it again
and it still not working, the only way is doing
conditional format on individual rows, which will take
forever.

Please can anyone assist forther

thanks

Geoff

 

[Andy B]

Geoff

I've just tried doing the whole column and it works fine (Office 2K). What
result are you getting? If you CF a single cell does that work?

Andy

 

[Guest]

Andy

I am using the same version, when i do the whole column
it just makes them all red, when i do it singly it works
fine.

Thanks for you help

Geoff

in the CF i have and it works fine singly but not multiple
cells
condition1 - formula is - =and(A1<=today(),B1<1)
condition2 - formula is - =or(A1<today(),b1<0)
Condition3 - Cell Value is - Greater than - =today()
as the requester has changed there mind about a few thing.

 

[Andy B]

Geoff

It will make the whole column red if the A cell is empty and the B cell is
less than 1. That is correct. That is what the CF should do. It did that for
me!!

Andy.

 

[Geoff]

Sorry you have missunderstood.

e.g.
i have a colume with dates in from 01/02/2004 all the way
down till 01/03/2004 then i have a second column with 0's
and 1's in, when i do a bulk CF selecting all the dates
all dates go red but when i do indiviual the date before
today are red if there is a 0 in the column beside it and
turn green if the date is before today and has a 1 in, if
the date is after today then the fill colour stays as
white.

Thanks

Geoff

 

[Andy B]

Geoff

If your data is not sensitive, send me your sheet and I'll have a look at
it. See if I can suss it out!!

Andy.

 

[geoffrobinson]

Hi Andy

i have created a sheet to mess about with so i don't
mess info up on the precess sheet, there data is not
sensative, i have tried e-mailing you but i got an
undeliverable message back.

Please can you send an e-mail to me and i will reply to
your e-mail

thanks

Geoff