conditional formatting formula

[Jack Sons]

Hi all,

In row 2 I have dates, E2 contains April 4 2005, F2 April 5 2005, G2 April 6
2005 etc.
In A4 to A40 I have names and in E4 to CR40 I have an entry where an
appointment is made. For instance, in A7 I have "Maas" and I have an
appointment with Maas at April 6, then in G7 will be a non numerical entry
(for instance "H 14.00 London" or some other text).

In the row of each name there should be an appointment for today or any
future date, that is what I want to check.

If a name has no appointment for "today" (I mean the day I am looking to the
spread sheet) nor for any later date, I want to put the name in red and bold
by means of conditional formatting. What conditional formatting formula do I
have to use for A4 to A40?

Jack Sons
The Netherlands

 

[Bernie Deitrick]

Jack,

=SUMPRODUCT(($E$2:$CR$2>=NOW())*(E3:CR3<>""))=0

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Jack,

Sorry. You will need to change the NOW to TODAY.

HTH,
Bernie
MS Excel MVP

 

[Jack Sons]

Bernie,

Thanks for your answer.

I also kept trying myself, off course. One minute before I saw your solution
I arrived at

=OFFSET(A4;25-ROW();MATCH(LOOKUP(REPT("z";255);4:4);4:4;0)-1)

which also works but is definitely much less elegant than your simple
formula.

Thanks again.

Jack.

 

[Jack Sons]

Bernie,

In fact I arrived at
=OFFSET(A4;25-ROW();MATCH(LOOKUP(REPT("z";255);4:4);4:4;0)-1)<TODAY()
so I also changed your NOW in TODAY, but NOW works as well.

Jack.

 

[Bernie Deitrick]

Jack,

Actually, NOW won't work for today's entries, since NOW() returns a decimal
that is greater than the integer that TODAY() returns, and the dates that
you have in row 2 are integers.

HTH,
Bernie
MS Excel MVP

 

[Jack Sons]

Bernie,

Thanks, I did not notice.

Jack.

Jack,

Actually, NOW won't work for today's entries, since NOW() returns a
decimal
that is greater than the integer that TODAY() returns, and the dates that
you have in row 2 are integers.

HTH,
Bernie
MS Excel MVP