conditional formatting

[griffitd]

hi

i have the folowing in excel, 12 rows , 1 column

07-jan-2012
08-jan-2012
09-jan-2012
10-jan-2012
11-jan-2012
12-jan-2012
13-jan-2012
14-jan-2012
15-jan-2012
16-jan-2012
17-jan-2012
25-jan-2012

i have coloured the forecolour based on > Today is red, > today + 2 weeks is yellow else black using conditional formatting. dates comparing against are in other cells.

what i want to do is revert the text back to black if each of the 12 rows has a value.

Any ideas thanks

 

[SkipVought]

hi,

I don't understand. You have TWO conditions in your CF: 1) > today + 14, 2) > today

The 3) third condition is the unstated condition.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[griffitd]

sorry this is excel 2003
i have three conditions:
> Today + 7
> Today + 14
> Today + 21

this is the same for all 12 rows.

what i what to do is ignore the formatting if all 12 rows have a date entered.

Thanks

 

[SkipVought]

for instance
[tt]
=and(> Today + 7,count(A:A)<12)
[/tt]
assuming that your 12 date values are in column A and ONLY those values are in column A.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

The CF result depends on the order of conditions. Excel applies the first TRUE condition. So in your last example, if the date is today+20, the first (>Today+7) is true and its format applied.

combo

 

[SkipVought]

Actually...
[tt]
=and(A1>Today()+7,Count(A:A)<12)
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}