Date Range with Holidays and Weekdays

I think you want to read up this article:
http://weblogs.sqlteam.com/dang/archive/2010/07/19/calendar-table-and-datetime-functions.aspx

not sure what exactly you want to achieve, but that should give you food for thought

I would think it should not be that complicated. This code that I got from this site lists all dates between holdiays. I'd like to further enhance it by only showing the dates that affect the weekday or weekdays:


Dim strMsg As String

   If Target.Column = 17 Then

   startdate = ActiveCell.EntireRow.Cells(12).Value
    enddate = ActiveCell.EntireRow.Cells(14).Value
   
     For Each cel In Range("AM10:AM" & Range("AM" & Rows.Count).End(xlUp).Row)
   
        If cel.Value >= startdate And cel.Value <= enddate Then
         Results = Results & cel.Offset(, 1) & vbTab & cel & vbCrLf
         
         End If
         
    Next cel
   
    MsgBox Results


Thanks.

Anyone?

Tracyms,

It is not clear to me what you are wanting to accomplish from your sample sheet.  You have a start date and end date columns that are multiple weeks.  Then you have columns for Mon - Sun.  Then you have some holiday columns.  I don't see how all these are related and exactly what you want with the different holiday columns.  I think you need to much more clearly define what you need to get some assistance.

-Bear

buttersk,

Yes, that's exactly what I was looking to do! It worked great in your spreadsheet but getting an error on mine. I use excel 2007. See comments in bold.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
    Dim holidayTable As ListObject
    Dim aCell As Range
    Dim x As String
    Dim inRange As Boolean
    Dim CheckColumn As Long
    Dim Message As String
    Dim inTableRange As Range
   
   
   
   
   
    If Target.Column <> 11 Then
        Exit Sub
    End If
   
    Set holidayTable = Sheets("Sheet1").ListObjects("HolidayTable") - getting out of range error   '   here make sure that we are selecting a cell in the table

***I named my table range "HolidayTable" and range is  =Sheet1!R10C1:R199C42****
   
     Set inTableRange = Intersect(Target, holidayTable.DataBodyRange)
    If inTableRange Is Nothing Then
        Exit Sub
    End If
   
    ' Ok... now we have selected a  "Click here to view" cell... lets check it out
   
    For Each aCell In holidayTable.ListColumns(41).Range <--list of holidays in column 41
        'Skip header row
        If (aCell.Row <> 9) Then <---skip header row 9         If testRange(Target.Row, aCell.Value10) Then <---my data rows start at row 10
           
            Select Case Weekday(aCell.Value10)
           
                Case vbMonday
                    CheckColumn = 17
                Case vbTuesday
                    CheckColumn = 18
                Case vbWednesday
                    CheckColumn = 19
                Case vbThursday
                    CheckColumn = 20
                Case vbFriday
                    CheckColumn = 21
                Case vbSaturday
                    CheckColumn = 22
                Case vbSunday
                    CheckColumn = 23
            End Select
           
            If Cells(Target.Row, CheckColumn) <> "" Then
                Message = Message & vbCrLf & Format(aCell.Value10, "mm/dd/yy")
            End If
        End If
        End If
       
    Next
   
    If Message = "" Then
        MsgBox ("No Holidays Fall in this range")
    Else
        MsgBox ("These Holidays fall in this range" & vbCrLf & Message)
    End If
   
End Sub

Function testRange(tableRow As Long, datetoCheck As Date) As Boolean

    If datetoCheck >= Cells(tableRow, 6) And datetoCheck <= Cells(tableRow, 8) Then <--start date is column 6, end date is column 8        testRange = True
    Else
        testRange = False
    End If
End Function

I got it working, I made the range a table...for some reason I didn't take you literally about the table. I was thinking it had to do with "ListObject" or something - I'm such a newb!  Thanks!

buttersk,

My full code is below and working great. Can I change the message to include text in column 44 next to the holidays? For example, this is how the message box is now:

These holidays fall in this range:

11/27/13
11/28/13

Please adjust your schedule.

Can I include the text next to the dates - 3 columns to right (column 44) so it'll read:

These holidays fall in this range:

11/27/13 - Thanksgiving holiday
11/28/13 - Thanksgiving holiday

Please adjust your schedule.

I’ve posted it as another question worth 500 points here:

https://www.experts-exchange.com/questions/28040729/Holiday-Date-and-Text.html

Fyi - posted another question about this topic:

https://www.experts-exchange.com/questions/28422734/Holiday-Table-on-Separate-Sheet.html