Conditional Formatting #2 is overwriting #3

[Jarod]

I have 3 Conditional Format Formulas, 1 of which is not allowing the other to
be applied. Here are the formulas:

#1 =ISBLANK($B$6) -> no format
#2 =$B$9<=(TODAY()+3) -> orange fill
#3 =$B$9<=(TODAY()+7) -> yellow fill

When the cell shows within 3 days, the cell is orange. It remains orange
when the date is moved past 3 days as well. I've tried reversing the order
of the formulas and colors, but that results in the fill being yellow,
regardless of the days.

Is it a conflict between the formulas? I can't think of a better way to
write them.

 

[Jon Peltier]

Conditional formats in Excel 2003 and earlier are tested sequentially, and
as soon as one conditional format is applied, the others are ignored. But
there may be more:

Which cell is this the format for, B6 or B9?
What is the default format of the cell?
What is a typical value of B9?
Is it really a date, or does Excel interpret is as text (i.e., numerical
value of zero, which is less than today's date)?

- Jon

 

[Jarod]

Sorry for the confusion. The subject of the formatting is B6, but B9 is what
the formatting is looking at for the changes. The date is auto-formatted in
cell B9, so it's reading it as a date (I'd imagine). B9 will show as
5/19/2008, but I enter 5/19 when I input dates. By default, the text is
bolded, but that is the only standard default formatting. Everything else is
conditional.

 

[David Biddulph]

If B6 is non-blank, and B9 is >TODAY()+3 but <=TODAY()+7 (for example,
19/5/08), then those CF conditions give a yellow result.