How about this.
Private Sub Workbook_Open()
    Dim cNum As Long
    If Weekday(Date - 1, vbMonday) > 5 Then
    Else
        cNum = Application.Match(CDbl(Date) - 1, Sheet2.Range("8:8"), 0) 'Change Sheet reference to your actual sheet.
        Sheet2.Select
        ActiveWindow.ScrollColumn = cNum
    End If
End Sub