Excel Scroll to dated column

[Simont485]

HI all

I use excel for a staff rota.

The dates are from D4 across to FF4 or whatever D4 plus 365 is......but it is a long way to scroll across!
Columns AB&C are for names, information etc....these don't scroll as they are frozen(freeze panes)

I was wondering this:

I would like to enter a date, in say Cell A1, and the spreadsheet scrolls across to that date and be next to column C?

Hope that makes sense!!!​

 

[JustynaMK]

Hi Simon,

In order to do that we need to introduce lngToday:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lngMatch            As Long
    Dim lngToday            As Long


    If Not Intersect(Target, Range("A1")) Is Nothing Then
        On Error Resume Next
            lngMatch = Application.Match(Range("$A$1"), Rows(4), 0)
            lngToday = Application.Match(CLng(Date), Rows(4), 0)
        On Error GoTo 0
        If lngMatch > 0 Then
            ActiveWindow.ScrollColumn = Cells(4, lngMatch).Column
        ElseIf lngToday > 0 Then
            ActiveWindow.ScrollColumn = Cells(4, lngToday).Column
        End If
    End If
End Sub

Same as previously, remember that this is a Change Event, meaning that cell A1 needs to be changed in order to be activated (e.g. select it and press delete).