Excel - jump to a specific column according to today's date using VBA

Question

I try to adjust the cursor to the matching date column of an excel worksheet. It seems to me that all the 'Find' or 'Match' functions not working correctly.

I´m using Excel 2007.

The Date values are in the range from R10:HU10 in the following format: DD.MM.YYYY, formatted user defined (also tried date and text - no difference). Today's date is in cell (CI10).

The worksheet window is freezed in Column 'Q' - the last column before the date entries starts.

What I tried to accomplish is to scroll to the right so column 'CI10' will be next to Column 'Q'

For testing reasons I tried a solution to color the specific column with the following VBA code:

Private Sub Worksheet_Activate()

  Dim TodaysDate As Date
  Dim Rng As Range

  TodaysDate = Date
  With Rows("10:10")
  Set Rng = .Find(what:=TodaysDate, _
  after:=.Cells(.Cells.Count), _
  LookIn:=xlFormulas, _
  lookat:=xlWhole, _
  SearchOrder:=xlByColumns, _
  SearchDirection:=xlNext, MatchCase:=False)
  If Not Rng Is Nothing Then
    Rng.EntireColumn.Interior.Color = vbMagenta
  Else
    'Give a message that today's date was not found
    MsgBox "Nothing found"
  End If
  End With
End Sub

It didn't work because Rng will always be 'Nothing'.

Any help will be highly appreciated especially hints about the correct comparison method between the system Date and the Date entries in the search range

Answer 1

I did this and it worked fine. dateRange could be something like "A1:H1" and current data is typed into cell H1, the script jumpes to cell H1 when run.

Sub jumpToDate()
Dim c As Range
Dim d As Date
d = Date
    For Each c In Range("dateRange")
        If c = d Then
            c.Select
        End If
    Next c
End Sub

I assume this is what you were looking for, just reading the title.

i'm using non English/excel date format (dd/mm/YYYY) which normally gives me headache working date format, but excel managed to handle the date correctly anyways

Answer 2

I suggest you search for the date in the cells' values, rather than in the formula used to calculate the cells.

Therefore change your LookIn:=xlFormulas to be LookIn:=xlValues.

Private Sub Worksheet_Activate()
    Dim TodaysDate As Date
    Dim Rng As Range

    TodaysDate = Date
    With Rows("10:10")
        Set Rng = .Find(what:=TodaysDate, _
                        after:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        lookat:=xlWhole, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not Rng Is Nothing Then
            Rng.EntireColumn.Interior.Color = vbMagenta
            'Set the window so that the date is in the top-left corner 
            Application.GoTo Rng, True
        Else
            'Give a message that today's date was not found
            MsgBox "Nothing found"
        End If
    End With
End Sub