Public Function IsInArray(intToBeFound As Integer, arr As Variant) As Boolean
    Dim i
    For i = LBound(arr) To UBound(arr)
        If arr(i) = intToBeFound Then
            IsInArray = True
            Exit Function
        End If
    Next i
    IsInArray = False

End Function

Function IsInteger(ByVal Value As Variant) As Boolean
    IsInteger = IIf(Int(Value) = Value, CInt(Value), False)
End Function

Sub DataColorIntervals()

    ' Range Variables
    Dim time As String
    time = "00"
    Dim timeLast As String
    timeLast = "00"
    Dim timeRange As Integer
    timeRange = 0
    Dim timeFirst As String
    timeFirst = "00"

    Dim Count As Integer
    Dim flipCount As Integer
    flipCount = 0
    Dim colorFlip As Boolean
    colorFlip = False

    ' Table format rows
    Dim startRow As Integer
    startRow = 1

    Dim maxCalls As Integer
    Dim maxCallsRow As Integer
    Dim maxRange

    ' Time intervals
    Dim flipArr(11) As Integer ' 5 Minutes
    For Z = 1 To UBound(flipArr)
        flipArr(Z) = Z * 5
    Next Z

    'Dim flipArr(4) As Integer ' 15 Minutes
    'For Z = 0 To UBound(flipArr)
    '    flipArr(Z) = Z + 15
    'Next Z

    Dim flipArr15(3) As Integer
    For Z = 1 To UBound(flipArr15)
        flipArr15(Z) = Z * 5
    Next Z

    Dim lastRow As Integer

    Dim wsNum As Integer
    wsNum = ThisWorkbook.Worksheets.Count

    Dim starting_ws As Worksheet
    Set starting_ws = ActiveSheet 'remember which worksheet is active in the beginning
    ThisWorkbook.Worksheets(3).Columns("H:N").Clear


' --------------------------------------------------------------------
    ' Workbook loop
    For x = 4 To wsNum
        ' ThisWorkbook.Worksheets(x).Activate
        ' MsgBox ("Hi")
        lastRow = ThisWorkbook.Worksheets(x).Cells(Rows.Count, "A").End(xlUp).Row
        ThisWorkbook.Worksheets(x).Cells.ClearFormats
        ThisWorkbook.Worksheets(x).Columns(11).Clear
        timeFirst = "00"
        maxCalls = 0
        maxCallsRow = 1

        Count = 2
        flipCount = 0

    ' --------------------------------------------------------------------
        ' Table Setup
        ThisWorkbook.Worksheets(3).Cells(startRow, 9).Interior.ColorIndex = 17
        ThisWorkbook.Worksheets(3).Cells(startRow, 10) = "Average Wait" + vbNewLine + "in Interval"
        ThisWorkbook.Worksheets(3).Cells(startRow, 10).Interior.ColorIndex = 17
        ThisWorkbook.Worksheets(3).Cells(startRow, 11) = "Maximum Wait" + vbNewLine + "in Interval"
        ThisWorkbook.Worksheets(3).Cells(startRow, 11).Interior.ColorIndex = 17
        ThisWorkbook.Worksheets(3).Cells(startRow, 12) = "Time to Destination" + vbNewLine + "in Interval"
        ThisWorkbook.Worksheets(3).Cells(startRow, 12).Interior.ColorIndex = 17
        ThisWorkbook.Worksheets(3).Cells(startRow, 13) = "Number of Calls" + vbNewLine + "in Interval"
        ThisWorkbook.Worksheets(3).Cells(startRow, 13).Interior.ColorIndex = 17

        ThisWorkbook.Worksheets(3).Cells(startRow, 8) = Mid(ThisWorkbook.Worksheets(x).Cells(startRow + 1, 1), 1, 10) & " " & Mid(ThisWorkbook.Worksheets(x).Cells(startRow + 1, 1), 21, 4)
        ThisWorkbook.Worksheets(3).Cells(startRow, 8).Interior.ColorIndex = 17
        ThisWorkbook.Worksheets(3).Range("H" & CStr(startRow + 1) & ":" & "M" & CStr(startRow + 4)).Interior.ColorIndex = 37
    ' --------------------------------------------------------------------
        ' Interval highlights
        While Count < lastRow + 1

            timeLast = time
            time = Mid(ThisWorkbook.Worksheets(x).Cells(Count, 1), 15, 2)

            ' MsgBox (time)
            '' Check for 15 minute intervals
            'If Mid(Time, 15, 2) = "00" And Mid(Time, 13, 4) <> Mid(TimeLast, 13, 4) Then
            '    colorFlip = Not colorFlip
            'ElseIf Mid(Time, 15, 2) = "15" And Mid(Time, 13, 4) <> Mid(TimeLast, 13, 4) Then
            '    colorFlip = Not colorFlip
            'ElseIf Mid(Time, 15, 2) = "30" And Mid(Time, 13, 4) <> Mid(TimeLast, 13, 4) Then
            '    colorFlip = Not colorFlip
            'ElseIf Mid(Time, 15, 2) = "45" And Mid(Time, 13, 4) <> Mid(TimeLast, 13, 4) Then
            '    colorFlip = Not colorFlip
            'Else
            '    End If

            '' Check for 5 minute intervals
            timeRange = CInt(time) - CInt(timeFirst)
            ' MsgBox (timeRange)
            If (time <> timeLast And IsInArray(CInt(time), flipArr)) And Count > 2 Or timeRange > 4 Then
                colorFlip = Not colorFlip
                timeFirst = time
                ThisWorkbook.Worksheets(x).Cells(Count - 1, 11) = flipCount
                ThisWorkbook.Worksheets(x).Cells(Count - 1, 11).Interior.ColorIndex = ThisWorkbook.Worksheets(x).Cells(Count - 1, 1).Interior.ColorIndex
                If flipCount > maxCalls Then
                    maxCalls = flipCount
                    maxCallsRow = Count - 1
                    ' MsgBox (CStr(maxCalls) & " " & CStr(x))
                    ' MsgBox (maxCallsRow)
                Else
                    End If

                flipCount = 1

            Else
                flipCount = flipCount + 1
                End If

            ' Set Colors
            If colorFlip = True Then
                ThisWorkbook.Worksheets(x).Cells(Count, 1).Interior.ColorIndex = 17
            Else
                ThisWorkbook.Worksheets(x).Cells(Count, 1).Interior.ColorIndex = 37
                End If

            Count = Count + 1



        Wend

        ThisWorkbook.Worksheets(3).Cells(startRow + 1, 8) = "Max Calls Interval:" ' & ": " & CStr(maxCalls)
        maxRange = ThisWorkbook.Worksheets(x).Range("G" & CStr(maxCallsRow - maxCalls + 1) & ":" & "G" & CStr(maxCallsRow))
        ThisWorkbook.Worksheets(3).Cells(startRow + 2, 10) = WorksheetFunction.Sum(maxRange) / maxCalls
        ThisWorkbook.Worksheets(3).Cells(startRow + 2, 10).NumberFormat = "0.00"
        ThisWorkbook.Worksheets(3).Cells(startRow + 2, 11) = WorksheetFunction.Max(maxRange)
        maxRange = ThisWorkbook.Worksheets(x).Range("G" & CStr(maxCallsRow - maxCalls + 1) & ":" & "H" & CStr(maxCallsRow))
        ThisWorkbook.Worksheets(3).Cells(startRow + 2, 12) = WorksheetFunction.Sum(maxRange) / maxCalls
        ThisWorkbook.Worksheets(3).Cells(startRow + 2, 12).NumberFormat = "0.00"
        ThisWorkbook.Worksheets(3).Cells(startRow + 2, 8) = Mid(ThisWorkbook.Worksheets(x).Cells(maxCallsRow - maxCalls + 1, 1), 12, 5) & " To " & Mid(ThisWorkbook.Worksheets(x).Cells(maxCallsRow + 1, 1), 12, 5)
        ThisWorkbook.Worksheets(3).Cells(startRow + 2, 13) = maxCalls

        startRow = startRow + 7

    Next x

    ThisWorkbook.Worksheets(3).Columns("H:N").AutoFit ' autofit columns
    ThisWorkbook.Worksheets(3).Columns("I").ColumnWidth = 1
    ThisWorkbook.Worksheets(3).Columns("J:N").HorizontalAlignment = xlCenter

    starting_ws.Activate 'activate the worksheet that was originally active

End Sub