Needs Help on the VBA for an Attendance Sheet

Hi ONG ZHEN 

Yes you have right  - please ignore the previous code and see below 

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

Dim coursDate As Date

With sWs

'counts number of names
NoNames = .Cells(50000, 1).End(xlUp).Row - 1

'looping through column 3 to 5 (step 1)
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

.Cells(c + 1, columnNo).Interior.ColorIndex = 3

Else

'taking data from attendance list
coursDate = Application.WorksheetFunction.SumIfs(aWs.Columns(3), aWs.Columns(1), .Cells(c + 1, 1).Value, aWs.Columns(2), .Cells(c + 1, columnNo).Value)

' apply cells colours according to the guidelines
If coursDate > CDate(.Cells(c + 1, 6)) Then
.Cells(c + 1, columnNo).Interior.ColorIndex = 6
ElseIf coursDate = 0 Then
.Cells(c + 1, columnNo).Interior.ColorIndex = 3
End If
End If
End If
Next c
Next columnNo


End With

End Sub