Consolidate used student hours into master sheet for export

I've developed a spreadsheet that has evolved over several years to help keep track of the number of student contact hours that have been used to help flag up when students have run out / are running out of allocated hours (so that additional hour requests can be made if necessary).

There is a master workbook, which contains the VBA (below) and several worksheets. There is a "Master" worksheet, which is used for the consolidated view. This has columns for the students name, allocated hours, total hours used and then several columns with one for each month.

The workbook then has another worksheet for each tutor. The tutor worksheets are almost the same as the master sheet, however they have an additional column which is the total number of hours used by that tutor.

The process flow is essentially each tutor works from an xlsx file that has a single sheet with their students on it. If they are allocated a new student during the month, they will add the student on to their spreadsheet and add hours as appropriate. At the end of the month, they email their sheet to the administrator who copies the contents of their sheet into their sheet on the master workbook. Once all of the sheets have been updated, the administrator runs the DSA_Total_Hours_Used macro, which consolidates the student information into the master sheet and then updates the individual tutor sheets and exports a new version of the individual tutors xlsx files that can be emailed back to them by the administrator.

The code itself does the several things:

• Clears the master sheets tracked hours (these will be repopulated during the processing)

• Sanity checks the Allocated hours columns (it should be populated in the master for all students).

• Updates the TotalHours used column on the master to include a formula that sums the monthly totals

• For each student on any of the tutor sheets it attempts to match the students name with one of the students on the master sheet. If the student c

Couple of suggestions on what I think is a really well done job.
I like the way you use subroutines, indents and with blocks

My first glance suggestions - will come back later and add more if I have time

Changes to declarations

' Specify Type of Constant
Const StudentNameColumn             As Integer = 1
Const AllocatedHoursColumn          As Integer = 2
Const TotalHoursUsedColumn          As Integer = 3
Const HoursUsedByTutorColumn        As Integer = 4
Const HoursUsedByThisTutorColumn    As Integer = 5
Const FirstPayCutoffColumn          As Integer = 6
Const LastPayCutoffColumn           As Integer = FirstPayCutoffColumn + 11
Const SumsRow                       As Integer = 1
Const HeadingsRow                   As Integer = 2
Const MinStudentRowTutorSheet       As Integer = 3
Const MaxStudentRowTutorSheet       As Integer = 100
Const MinStudentRowMasterSheet      As Integer = 3
Const MaxStudentRowMasterSheet      As Integer = 300

Private NameMissingFromMaster       As Boolean

' Change from generic Object
Private MasterSheet                 As Worksheet

Changes to your Setupformatting subroutine

Private Sub Setupformatting(sheet, firstRow, lastRow)
    ' Add two variable declarations
    Dim strAddrAllocHrs As String
    Dim strAddrTotalHrs As String

    ' Modify top of code to
    ' - use With Sheet
    ' - Remove select
    ' - store addresses
    With sheet
        .Select
        ' Remove This Line
        'sheet.Cells(firstRow, TotalHoursUsedColumn).Select

        ' Store two addresses in variables
        strAddrAllocHrs = .Cells(firstRow, AllocatedHoursColumn).Address(False, True)
        strAddrTotalHrs = .Cells(firstRow, TotalHoursUsedColumn).Address(False, True)
    End With

    ' Replace specific address references with pre determined variables
    With Range(sheet.Cells(firstRow, TotalHoursUsedColumn), sheet.Cells(lastRow, TotalHoursUsedColumn))
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF(ISBLANK(" & strAddrTotalHrs & "),FALSE," & _
          strAddrAllocHrs & " <= " & _
          strAddrTotalHrs & ")"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .Color = vbRed
                .TintAndShade = 0
            End With
        End With
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF(ISBLANK(" & _
          strAddrTotalHrs & "),FALSE," & _
          strAddrAllocHrs & "<=(" & _
          strAddrTotalHrs & "+(" & _
          strAddrAllocHrs & "/30 * 5)))"
        With .FormatConditions(.FormatConditions.Count)
            With .Interior
                .PatternColorIndex = xlAutomatic
                .Color = vbYellow
                .TintAndShade = 0
            End With
        End With
    End With
End Sub

StackExchange Code Review Q#131623, answer score: 3