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

VBA ColorIndex Property of Excel VBA is very useful to set the fill colors, border colors and font colors. Excel VBA ColorIndex returns index values from 1 to 56, -4105 and -4142. You can set the default colors using VBA enumeration number -4105 ( or xlColorIndexAutomatic). We can set VBA...

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

VBA ColorIndex Property of Excel VBA is very useful to set the fill colors, border colors and font colors. Excel VBA ColorIndex returns index values from 1 to 56, -4105 and -4142. You can set the default colors using VBA enumeration number -4105 ( or xlColorIndexAutomatic). We can set VBA...

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