conditional formatting

[Blake 7]

Hi Guys

Cells B10:B107 contain the formula

=IF(O10="","",NETWORKDAYS(TODAY(),O10)&" days ")

which acts as (in this case) a deadline countdown which tell the user how many days there are left to respond to a complaint.

I wanted to add conditional formatting so it is very clear at a glance which ones are urgent.

Red = <=5
Amber = Between 6 & 9
Green = >=10

The issue i am encountering is the the Blank Cells within the range b10:b107 (blank as Col 0 is unpopulated) are appearing as (Format) green I would like them to be left without formating! Any ideas on how to achieve this ? cheers

See database tab of attachment col B

Thanks for your response.

[NBVC]

Change the Red condition to: use a formula to determine which cells to format and use formula:

=AND(B10<>"",B10<0)

[Fotis1991]

http://www.excelforum.com/excel-gene...ormatting.html

[Blake 7]

Hi N, Thanks for your help.

I changed the red to

=AND(B10<>"",B10<0)

and the green to

=AND(B10<>"",B10>=10)

This worked re the formatting issue and now the blank cells are un formatted - however, minus numbers ie deadlines in the past are formatting as green.. how can this be so as a minus value is <10... !! any clue?

@ fotis, hi there and thanks for your input. Before posting this thread i put a note on the previous thread advising that it was closed.

[NBVC]

Try changing formula in column B to:

=IF(O10="","",NETWORKDAYS(TODAY(),O10)) so you are dealing with actual numbers.

and then format as Custom:

General " days"

[Fotis1991]

OK..! Nothing personal, but for me is the same requietment.

This mean duplicate post!!..

As the Moderator does not thinks so, then for me is OK...!

[Blake 7]

You legend - perfect

[NBVC]

I closed the other thread to eliminate confusion.