I have a column of dates (assume A1:A45) and I would like to highlight the
first date in the column (reading top to bottom) that is equal to, or
nearest to a specified date (in Cell B1), but NOT less than the specified
date.

The dates will all be from the same month and will be (should be!) in
chronological order, reading top to bottom. The same date may appear more
than once in the column. Some dates will not appear at all.

For example:

A1 02/04/2008
A2 03/04/2008
A3 05/04/2008
A4 05/04/2008
A5 10/04/2008
A6 13/04/2008
A7 13/04/2008
A8 17/04/2008
A9 26/04/2008
etc.

If the specified date is 05/04/2008, Cell A3 would be highlighted.

If the specified date is 11/04/2008, Cell A6 would be highlighted.

If the specified date is 18/04/2008, Cell A9 would be highlighted.

If the specified date is 27/04/2008, and there are no further dates entered
beyond A9, then no cells would be highlighted.

Is this achievable without getting too complicated?

Am I looking at Conditional Formatting and a helper column?

TIA
--
Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

No helper column needed.

Select A1 to A45, where the focus of the selection is A1 (colored white).

Then, in Conditional Formatting, use "Formula Is", and enter this formula:

=A1=INDEX($A$1:$A$45,MATCH(TRUE,($A$1:$A$45>=$B$1) ,0))

Click on "Format", and choose whatever you like to highlight the dates.

BTW - if you have duplicates, this will highlight *all* the dates that meet
the criteria.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"mlv" <mike.safetycatchvincent@jet.co.uk> wrote in message
news:g149tl$mv$1@north.jnrs.ja.net...
>I have a column of dates (assume A1:A45) and I would like to highlight the
>first date in the column (reading top to bottom) that is equal to, or
>nearest to a specified date (in Cell B1), but NOT less than the specified
>date.
>
> The dates will all be from the same month and will be (should be!) in
> chronological order, reading top to bottom. The same date may appear more
> than once in the column. Some dates will not appear at all.
>
> For example:
>
> A1 02/04/2008
> A2 03/04/2008
> A3 05/04/2008
> A4 05/04/2008
> A5 10/04/2008
> A6 13/04/2008
> A7 13/04/2008
> A8 17/04/2008
> A9 26/04/2008
> etc.
>
> If the specified date is 05/04/2008, Cell A3 would be highlighted.
>
> If the specified date is 11/04/2008, Cell A6 would be highlighted.
>
> If the specified date is 18/04/2008, Cell A9 would be highlighted.
>
> If the specified date is 27/04/2008, and there are no further dates
> entered beyond A9, then no cells would be highlighted.
>
> Is this achievable without getting too complicated?
>
> Am I looking at Conditional Formatting and a helper column?
>
> TIA
> --
> Mike
> -Please remove 'safetycatch' from email address before firing off your
> reply-
>

RagDyer wrote:
>
> No helper column needed.
>
> Select A1 to A45, where the focus of the selection is A1 (colored white).
>
> Then, in Conditional Formatting, use "Formula Is", and enter this formula:
>
> =A1=INDEX($A$1:$A$45,MATCH(TRUE,($A$1:$A$45>=$B$1) ,0))
>
> Click on "Format", and choose whatever you like to highlight the dates.
>
> BTW - if you have duplicates, this will highlight *all* the dates that
> meet the criteria.
>
> --
> HTH,
>
> RD

Thanks RD, the formula works just fine.
--
Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

You're welcome, and thank you for the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"mlv" <mike.safetycatchvincent@jet.co.uk> wrote in message
news:g162pt$eb3$1@north.jnrs.ja.net...
> RagDyer wrote:
>>
>> No helper column needed.
>>
>> Select A1 to A45, where the focus of the selection is A1 (colored white).
>>
>> Then, in Conditional Formatting, use "Formula Is", and enter this
>> formula:
>>
>> =A1=INDEX($A$1:$A$45,MATCH(TRUE,($A$1:$A$45>=$B$1) ,0))
>>
>> Click on "Format", and choose whatever you like to highlight the dates.
>>
>> BTW - if you have duplicates, this will highlight *all* the dates that
>> meet the criteria.
>>
>> --
>> HTH,
>>
>> RD
>
> Thanks RD, the formula works just fine.
> --
> Mike
> -Please remove 'safetycatch' from email address before firing off your
> reply-
>

You could also use this formula

=A1=MIN(IF(A$1:A$45>=B$1,A$1:A$45))

"RagDyer" wrote:

> You're welcome, and thank you for the feed-back.
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "mlv" <mike.safetycatchvincent@jet.co.uk> wrote in message
> news:g162pt$eb3$1@north.jnrs.ja.net...
> > RagDyer wrote:
> >>
> >> No helper column needed.
> >>
> >> Select A1 to A45, where the focus of the selection is A1 (colored white).
> >>
> >> Then, in Conditional Formatting, use "Formula Is", and enter this
> >> formula:
> >>
> >> =A1=INDEX($A$1:$A$45,MATCH(TRUE,($A$1:$A$45>=$B$1) ,0))
> >>
> >> Click on "Format", and choose whatever you like to highlight the dates.
> >>
> >> BTW - if you have duplicates, this will highlight *all* the dates that
> >> meet the criteria.
> >>
> >> --
> >> HTH,
> >>
> >> RD
> >
> > Thanks RD, the formula works just fine.
> > --
> > Mike
> > -Please remove 'safetycatch' from email address before firing off your
> > reply-
> >
>
>
>