Needs Help on the VBA for an Attendance Sheet

Hi Ong Zhen

Find the explanation in the code comments

Option Explicit

Sub checkAttendance()

Dim sWs As Worksheet
Dim aWs As Worksheet
Set sWs = Worksheets("Summary")
Set aWs = Worksheets("Attendance list")
Dim columnNo As Byte

Dim NoNames As Long
Dim c As Long

With sWs

    'counts number of names
    NoNames = .Cells(50000, 1).End(xlUp).Row - 1
   
    'looping through column 3 to 5 (C, D, E)
    For columnNo = 3 To 5 Step 1
   
        'clean previous highlights to start from scratch
        .Range(.Cells(2, columnNo), .Cells(NoNames + 1, columnNo)).Interior.Color = xlNone
       
        'going through each name
        For c = 1 To NoNames
           
            ' ignore cells without course name
            If Trim(.Cells(c + 1, columnNo)) <> "" Then
           
                ' checking whether name exist in attendance sheet
                If Application.WorksheetFunction.CountIfs(aWs.Columns(1), .Cells(c + 1, 1).Value) = 0 Then
               
                        'write code here if you want highlight names that do not exist in the attendance list
                    Else
                   
                    ' apply cells colours according to the guidelines
                    If Application.WorksheetFunction.CountIfs(aWs.Columns(1), .Cells(c + 1, 1).Value, aWs.Columns(2), .Cells(c + 1, columnNo).Value, aWs.Columns(3), ">" & .Cells(c + 1, 6).Value) = 1 Then
                        .Cells(c + 1, columnNo).Interior.ColorIndex = 6
                    ElseIf Application.WorksheetFunction.CountIfs(aWs.Columns(1), .Cells(c + 1, 1).Value, aWs.Columns(2), .Cells(c + 1, columnNo).Value) = 0 Then
                        .Cells(c + 1, columnNo).Interior.ColorIndex = 3
                    End If
                End If
            End If
        Next c
    Next columnNo
    
   
End With

End Sub