Holiday Table on Separate Sheet

Yes, this what I'm looking for but trying to incorporate into my spreadsheet.  In an effort not to change too much of what's already in my code, can I do the following:

- Keep my table name the same (Table1)
- Call my new table (on the "Holiday" sheet) "HolidayList"
- In my HolidayList table I have:

Holiday date in Column 1 (A) =  Format(aCell.Value2, "mm/dd/yy")
Holiday Day in Column 2 (B) = Cells(aCell.Row, 45).Value
Holiday Name in Column 3 (C) = Cells(aCell.Row, 47).Value

So, in the above columns would be equal to the below  in my code:
     Message = Message & vbCrLf & Cells(aCell.Row, 45).Value & " - " & Format(aCell.Value2, "mm/dd/yy") & " - " & Cells(aCell.Row, 47).Value


Column 29 is the "Click to view Holidays" column, which is this in my code:

 If Target.Column <> 29 Then
        Exit Sub
    End If

 I shouldn't need this line in my code since the holidays will no longer be in the original table but rather on the Holidays sheet:

For Each aCell In holidayTable.ListColumns(44).Range
        'Skip header row
        If (aCell.Row <> 9) Then
         If testRange(Target.Row, aCell.Value2) Then

I hope it makes sense! :-)

 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 <> 29 Then
        Exit Sub
    End If
    
    
    Set holidayTable = Sheets("Courses").ListObjects("Table1")
    
 
    
'   here make sure that we are selecting a cell in the table
    
     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(44).Range
        'Skip header row
        If (aCell.Row <> 9) Then
         If testRange(Target.Row, aCell.Value2) Then
            
            Select Case Weekday(aCell.Value2)
            
                Case vbMonday
                    CheckColumn = 10
                Case vbTuesday
                    CheckColumn = 11
                Case vbWednesday
                    CheckColumn = 12
                Case vbThursday
                    CheckColumn = 13
                Case vbFriday
                    CheckColumn = 14
                Case vbSaturday
                    CheckColumn = 15
                Case vbSunday
                    CheckColumn = 16
                    
                   
            End Select
            
            

   
   
   If Cells(Target.Row, CheckColumn) <> "" Then
              
                
                Message = Message & vbCrLf & Cells(aCell.Row, 45).Value & " - " & Format(aCell.Value2, "mm/dd/yy") & " - " & Cells(aCell.Row, 47).Value
            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, 5) And datetoCheck <= Cells(tableRow, 18) Then
        testRange = True
    Else
        testRange = False
    End If
End Function

Select allOpen in new window

Unfortunately, no.  I based my changes on your workbook and the click in column 10 action of the code.  The names of lists/tables aren't really that important and can be easily changed.  Describe how you expect your users to interact with the Sheet1 worksheet and under what conditions you want to check the holiday dates.

Also, post a more representative worksheet.  Your last comment referenced columns that aren't populated in your prior upload.

I got it to work by studying your code. Thanks.