Selecting current date on a sheet

[Kenneth]

I have a column of dates and when I open the file I would like the current date to be showing in the screen so the user doesn't always have to scroll down to find it or use the find tool.

 

[Cam]

Try using:

=today()

 

[Kenneth]

Actually the column already has a list of dates out to about 2 years. I would like to select the cell that has the current date when it is open. I tried using find but it gave me error (object variable or with variable not set) when I tried to use "Date" in the "what" argument instead of actual text. Below is the code.
Private Sub Autpen()

Cells.Find(What:=Date, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate

End Sub

 

[Calypso]

Hi Kenneth,

Hope this does what you want. Add the code to your 'open' event.



Dim aRange As Range
Dim aCell As Range

Set aRange = Sheet1.Range("RangeOfDates")

For Each aCell In aRange
If CLng(aCell.Value) = CLng(Date) Then
aCell.Select
Exit For
Else
aCell.Offset(1, 0).Select
End If
Next aCell

 

[Yogi Anand]

On 2002-08-23 09:11, Kenneth wrote:
I have a column of dates and when I open the file I would like the current date to be showing in the screen so the user doesn't always have to scroll down to find it or use the find tool.

How about using conditional formating for the cells where the dates are already entered

for cell A1, use ... FORMULA IS ... =A1=today() -- then Format|Patterns|Color

Please post back if it works for you ... otherwise explain a little further and let us take it from there.

Regards!

Yogi

 

[Kenneth]

The code works perfectly and the conditional formatting added a nice touch to the project. I can't thank you enough. This is an awesome web page!!