Sub CheckAndMarkOvertime()
Dim ws As Worksheet
Dim nameRange As Range
Dim dailyHours As Double
Dim totalWorkHours As Double
Dim startTime As Date, endTime As Date
Dim timeDifference As Double
Dim timeParts() As String
Dim personName As String
Dim lastRow As Long, lastCol As Long
Dim firstRow As Long, firstCol As Long
Dim cellToCheck As Range
Dim dateCol As Long
Dim personRow As Long
Dim dateRange As Range
Dim currentDateCell As Range
Dim personDict As Object
Dim currentCell As Range
Dim hoursSum As Double
Dim i As Long
Set ws = ThisWorkbook.Sheets("Main")
dailyHours = ws.Cells(13, 3).value
With ws.UsedRange
lastRow = .Rows(.Rows.count).Row
lastCol = .Columns(.Columns.count).Column
End With
firstRow = 19
firstCol = 8
ws.Range(ws.Cells(firstRow, firstCol), ws.Cells(lastRow, lastCol)).Interior.Color = RGB(255, 255, 255)
Set nameRange = ws.Range("A" & firstRow & ":A" & ws.Cells(ws.Rows.count, 1).End(xlUp).Row)
For dateCol = 1 To (lastCol - firstCol + 1)
Set dateRange = ws.Range(ws.Cells(firstRow, firstCol + dateCol - 1), ws.Cells(lastRow, firstCol + dateCol - 1))
For personRow = firstRow To ws.Cells(ws.Rows.count, 1).End(xlUp).Row
personName = ws.Cells(personRow, 1).value
totalWorkHours = 0
Set personDict = CreateObject("Scripting.Dictionary")
For Each cellToCheck In dateRange
If cellToCheck.Row = personRow Then
If cellToCheck.value <> "" Then
If InStr(cellToCheck.value, "-") > 0 Then
timeParts = Split(cellToCheck.value, "-")
startTime = CDate(timeParts(0))
endTime = CDate(timeParts(1))
timeDifference = (endTime - startTime) * 24
Else
startTime = CDate(cellToCheck.value)
endTime = DateAdd("h", dailyHours, startTime)
timeDifference = (endTime - startTime) * 24
End If
totalWorkHours = totalWorkHours + timeDifference
End If
End If
Next cellToCheck
personDict(personName) = totalWorkHours
Next personRow
For Each currentCell In dateRange
personName = ws.Cells(currentCell.Row, 1).value
If personDict.exists(personName) Then
If personDict(personName) > dailyHours Then
ws.Cells(currentCell.Row, firstCol + dateCol - 1).Interior.Color = RGB(255, 255, 0)
End If
End If
Next currentCell
Next dateCol
End Sub