VBA Loop to check if a date is between two dates

Hello,
I have a VBA Macro that is checking whether or not a date is between two other dates (located on another tab) and if so, then returning the hours from the cell below. It should loop through the two rows of dates until it finds the date it is between and return the corresponding hours.
Below is the code:

Sub formula()
'
' formula Macro
'

'
Dim C As Integer, HoursColumn As Integer, PeriodColumn As Integer, DatesColumn As Integer
Dim R As Integer
Dim ColumnCount As Integer, RowCount As Integer
'Dim WeekDates As Range, PeriodStarts As Range, PeriodEnds As Range
Dim Job1 As Range
Dim Hours As String, WeekDates As String, PeriodStarts As String, PeriodEnds As String

ColumnCount = Range("B3").CurrentRegion.Columns.Count + 1
RowCount = Range("A5").CurrentRegion.Rows.Count
Set Job1 = Cells(5, 2)

    Windows("Dates.xlsm").Activate
    Sheets("Summary").Select
    Range("B5:AG12").Select
    Selection.ClearContents
    
    Job1.Select
    'For R = 1 To RowCount
        For C = 1 To ColumnCount
            WeekDates = Cells(3, 2 + DatesColumn).Value
            Hours = Worksheets("Jobs1-4").Cells(5, 2 + HoursColumn).Value
            PeriodStarts = Worksheets("Jobs1-4").Cells(2, 2 + PeriodColumn).Value
            PeriodEnds = Worksheets("Jobs1-4").Cells(3, 2 + PeriodColumn).Value
            If (PeriodStarts <> "") Then
                If (WeekDates >= PeriodStarts And WeekDates <= PeriodEnds) = True Then
                    ActiveCell.FormulaR1C1 = Hours / 4
                    ActiveCell.Offset(0, 1).Select
                    DatesColumn = DatesColumn + 1
                    PeriodColumn = 0
                    HoursColumn = 0
                ElseIf (WeekDates >= PeriodStarts And WeekDates <= PeriodEnds) = False Then
                    PeriodColumn = PeriodColumn + 1
                    HoursColumn = HoursColumn + 1
                Else
                    MsgBox "Error with Nested If"
                End If
            ElseIf PeriodStarts = "" Then
                PeriodColumn = PeriodColumn + 1
                HoursColumn = HoursColumn + 1
                
            Else
                MsgBox "Error with Original If"
                
            End If
        Next C
        'ActiveCell.Offset(1, (-ColumnCount)).Select
    'Next R
        
End Sub

When I step through the macro line by line it seems to work fine, however, the end result seems to provide only 3 result numbers (which two of the three are incorrect).
Please help. Thanks in advance!