I been trying to help my son with a task, which selects staff who have a start and finish time. He wanted to highlight their shift with a coloured/greyed background on a separate sheet.
I've simply put a spreadsheet together with two sheets and entered some basic VBA code to automate this process.

The problem occurs when I want to select the range that is dynamically created based on the staff members start and finishing times. I get the usual runtime error '1004' - application-defined or object-defined error.
Anybody know how I can get this to work, as I'm sure it must be something simple? I've attached the spreadsheet (macro-enabled with no password) but the code's here anyway.
BTW: The function at the head of the code supplies the column letter from column number of the selected cell.

Sheet1 has the following:
Name Start Finish
Name 1 9:00 AM 5:00 PM
Name 2 6:00 AM 6:00 PM
Name 3 12:00 PM 8:00 PM
Name 4 7:30 AM 4:30 PM

Sheet2
Name 6:00 AM 6:30 AM 7:00 AM 7:30 AM 8:00 AM (...with the series extended to 8:00 PM)
Name 1
Name 2
Name 3
Name 4 


Public Function wColNm(ColNum)
    wColNm = Split(Cells(1, ColNum).Address, "$")(1)
End Function

Sub TimeSlots()
    
    Dim ThisStaff As Integer
    Dim StaffSTm As String
    Dim StaffFTm As String
    Dim TimeSlot As Range
    TotalStaff = Range(ActiveCell.Address).End(xlDown).Row - 1
    Nm = 1
    For Staff = 1 To TotalStaff
        Sheets("Sheet1").Select
        Range("A" & Nm + Staff).Select
        StaffNm = ActiveCell.Value
        STm = ActiveCell.Offset(0, 1).Value
        FTm = ActiveCell.Offset(0, 2).Value
        Sheets("Sheet2").Select
        Sheets("Sheet2").Columns(1).Select
        Selection.Find(What:=StaffNm, After:=ActiveCell, LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False).Activate
        ThisStaff = ActiveCell.Row
        Sheets("Sheet2").Rows(1).Select
                Selection.Find(What:=CDate(STm), After:=ActiveCell, LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False).Activate
        StaffSTm = wColNm(ActiveCell.Column)
        Sheets("Sheet2").Rows(1).Select
                Selection.Find(What:=CDate(FTm), After:=ActiveCell, LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False).Activate
        StaffFTm = wColNm(ActiveCell.Column)
        StaffStart = StaffSTm & ThisStaff
        StaffFinish = StaffFTm & ThisStaff
        Set TimeSlot = Range(StaffStart, StaffFinish)
        Range(TimeSlot).Select
    Next Staff

End Sub