Search for a cell based on color ("go to")

  • Thread starter Thread starter Jan
  • Start date Start date
J

Jan

Hi,
I made sort of a calendar, in colom A are all the days of the year.
And "today" is colored automaticly. But now I want Excel to show me
where "today" is, so that I don't have to scroll down or up.
I would like to create a "button" with a macro that "goes to today".
Anyone knows how ?
thanks.
 
Hi Jan

Try this one

Sub FindToday()
On Error Resume Next
Application.Goto Cells.Find(What:="=TODAY()", _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True), True
On Error GoTo 0
End Sub

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
And if your dates are values, this might get you started:

Option Explicit
Sub testme()

Dim res As Variant

With Worksheets("sheet1")
res = Application.Match(CLng(Date), .Range("a:a"), 0)
If IsError(res) Then
MsgBox Date & " wasn't found!"
Else
Application.Goto .Range("a:a")(res), scroll:=True
End If
End With

End Sub
 
Great !!! it works
I made little adjustment because the search range was not Column A (as
I mentionned) but A11-A320. But after the change it still worked !!!!
In what kind of books can I find these sort of solutions ?
Excel-Novice or Expert, or in books about Visual Basics ?
Thanks a lot !
 
I'm not sure if any book would cover this kind of particular problem, but most
will give you the background so you could apply what you learned to almost any
problem.

A lot of people like John Walkenbach's book:
Excel 2003 Power Programming with VBA

You can find a whole list of books at Debra Dalgleish's site:
http://www.contextures.com/xlbooks.html

Maybe you could print it and visit your local bookstore to see if you can find
one you like.
 
Back
Top