Chip,

Thank you for the suggestion.

My current data does not meet criteria 2) as you note below but the
more I think about the problem I'm realizing that my date data
probably needs to be sequential, in fact the previous solution
provided by OssieMac will also have a problem with my current
structure.

CLng is new to me. I'll have to play with this to see how it works. It
is certainly a very simple solution.


On Sat, 25 Apr 2009 15:14:04 -0500, Chip Pearson
wrote:

If the following are true:

1) the first cell in the list of dates is earlier than the current
date,
2) dates are sequential with no skipped dates or blank cells,
3) the last cell in the list is greater than the current date,

you can use

Dim StartCell As Range
Dim TodayCell As Range
Set StartCell = Range("B3")
Set TodayCell = StartCell(CLng(Now) - CLng(StartCell), 1)

where B3 is the cell with the first date. The TodayCell will be set to
the cell in column B containing today's date.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Fri, 24 Apr 2009 20:13:00 -0600, John Keith wrote:

In column B I have a long list of sequential dates like below:

4/15/09
4/16/09
4/17/09
4/18/09
.
.
.
4/30/09
5/1/09

and so on.

In a macro I would like to identify which row contains todays date so
that I can perform some additional function on cells to the right of
this date.

I've tried a loop that searchs through column B like below but
obviously I don't have the right test:

If cells(i,2) = format(now(), "mm/dd/yy") then

I think the Find function might also work but I haven't a clue how to
do this.

Suggestions would be greatly appreciated. TIA


John Keith


John Keith