Search cells based on student name, cell background colour and return a tally for colour

At my school we're provided with national test data for entire year levels of students. Our task is to isolate some data for our own classes to hopefully improve student outcomes.

I've attached a spreadsheet which shows the type of format we're presented with. I just don't know how to calculate tallies based on cell background colours for just my class.

Explanation:
I have a class summary worksheet which lists individual students in my class (generally up to 30 students). On this same class summary sheet there are some cells to show tallies of achievement levels. These show how the class performed at the national test in reading, writing and numeracy.

The Year Level Summary worksheet has the results for the entire year level (usually over a hundred students). There are columns for student results in grade 3, 5, 7 and 9. This group only has results from grade 3 and 5.

There are five possible cell background colours from green to red. I'd like to search through the hundred or so students, finding the students in my class and returning the tally of each colour for their reading, writing and numeracy (this would be from their grade 5 test as it was the most recent test).

I've used the following formula to find colour tallies for the entire year level but I'm not sure how to produce a tally for just my class.

=CountCcolor('Year Level Summary'!E9:E107,'Year Level Summary'!H113)

The CountCcolor function (thanks to Raddini Rahayu):

Function CountCcolor(range_data As Range, criteria As Range) As Long
    Dim datax As Range
    Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
    If datax.Interior.ColorIndex = xcolor Then
        CountCcolor = CountCcolor + 1
    End If
Next datax
End Function

I hope this makes sense and I'd really appreciate any help anyone can give.

Thank you.

This Macro Creates a new sheet listing just your class

Sub Macro1()

    Sheets("Year Level Summary").Select
    Sheets("Year Level Summary").Copy Before:=Sheets("Year Level Summary")

    ActiveSheet.Name = "Year Level Summary (MyClass)"
    LR = Cells(Rows.Count, 2).End(xlUp).Row
    
    Range("AW9:AW" & LR).FormulaR1C1 = "=IF(ISNA(MATCH(RC[-46],'Student list'!C[-48],0)),0,1)"
    
    Range("AW9:AW" & LR).Value = Range("AW9:AW" & LR).Value
    
    Range("AW9:AW" & LR).UnMerge
    
    ActiveSheet.Sort.SortFields.Add Key:=Range("AW9:AW" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("B9:AW" & LR)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   
   
    Columns("AW:AW").Select
Set Z = Selection.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
        
    Range("A9:AW" & Z.Row - 1).Delete Shift:=xlUp
    
    Columns("AW:AW").Select

    Range("A4").Select
End Sub

Hi, drof_06,

according to Forum Rule 3 you would need to add code-tags to your procedure. Thanks in advance.

Ciao,
Holger

I don't know what workbook you tried my macro in.

it works perfectly in your sample.

The error would occur if none of your students names appeared in the Year Summary.

Run Macro1 in the attached workbook.