Fill cell colour according to percentile.

[XanderTheNotSoAwesome]

I have an excel sheet of students with their different subject scores for their tests.
Row: Student Names
Columns: Subject headers and their scores. (Math, Science, Literature.

Is there a excel macro that can automatically help me colour the cells of those in the top 20% green, next 20% light green, so on so forth until 20% bottom red? Only within each subject, not across the students. (Top 20% in Math, Top 20% in Science, etc)

 

[mrshl9898]

Like this?

Sub DoTheThing()

Dim sht As Worksheet
Dim LastRow As Long
Dim rownum As Long
Dim colnum As Long

Set sht = ActiveSheet

LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
rownum = 2
colnum = 2

Do Until colnum = 5
    Do Until rownum = LastRow + 1
        If Cells(rownum, colnum) >= 80 Then
            Cells(rownum, colnum).Interior.ColorIndex = 4
            ElseIf Cells(rownum, colnum) >= 60 Then
                Cells(rownum, colnum).Interior.ColorIndex = 43
            ElseIf Cells(rownum, colnum) >= 40 Then
                Cells(rownum, colnum).Interior.ColorIndex = 44
            ElseIf Cells(rownum, colnum) >= 20 Then
                Cells(rownum, colnum).Interior.ColorIndex = 45
            Else: Cells(rownum, colnum).Interior.ColorIndex = 3
        End If
    rownum = rownum + 1
    Loop
colnum = colnum + 1
rownum = 2
Loop

End Sub

More colours shown here:

VBA ColorIndex Property of Excel Range Shape Chart Background Color

analysistabs.com

 

[XanderTheNotSoAwesome]

Like this?

Sub DoTheThing()

Dim sht As Worksheet
Dim LastRow As Long
Dim rownum As Long
Dim colnum As Long

Set sht = ActiveSheet

LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
rownum = 2
colnum = 2

Do Until colnum = 5
    Do Until rownum = LastRow + 1
        If Cells(rownum, colnum) >= 80 Then
            Cells(rownum, colnum).Interior.ColorIndex = 4
            ElseIf Cells(rownum, colnum) >= 60 Then
                Cells(rownum, colnum).Interior.ColorIndex = 43
            ElseIf Cells(rownum, colnum) >= 40 Then
                Cells(rownum, colnum).Interior.ColorIndex = 44
            ElseIf Cells(rownum, colnum) >= 20 Then
                Cells(rownum, colnum).Interior.ColorIndex = 45
            Else: Cells(rownum, colnum).Interior.ColorIndex = 3
        End If
    rownum = rownum + 1
    Loop
colnum = colnum + 1
rownum = 2
Loop

End Sub

More colours shown here:

VBA ColorIndex Property of Excel Range Shape Chart Background Color

analysistabs.com

This worked perfectly! Thank you so much Sorry, as a field worker transitioned to Work-From-Home during this COVID period, I am entirely new to this whole thing and was suddenly assigned this job which I had not much clue on. Appreciate the help!

 

[mrshl9898]

Happy to help