The spreadsheet is a roster that I have to fill the cells with "07:00-19:00" or "19:00-07:00" which is the only two different shift times we have.
I wanted to create a "check" button that would run through all tables and display a message if any of the following is false:
- Monday (day) 5 employees one letter + 2 employees double letter
- Monday (night) 4 employees one letter + 2 employees double letter
- Rest of the week (day/night) 5 employees One letter + 1 employee double letter
- No one worked for more than 4 days straight.
I managed to think on the function bellow for the 4 days straight rule, but I couldn't think on something for the other 3 rules.
PS: I put the same info at all the weeks into the spreadsheet just to test while coding.
Public Function MaxConsecutiveDays(ByRef rRng As Range) As Long Dim rCell As Range Dim lCnt As Long Dim lMax As Long For Each rCell In rRng.Cells If IsEmpty(rCell.Value) Then lCnt = 0 Else lCnt = lCnt + 1 End If If lCnt > lMax Then lMax = lCnt End If Next rCell MaxConsecutiveDays = lMax End Function