Fill cell colour according to percentile.

Joined
May 20, 2021
Messages
26
Office Version
  1. 2016
Platform
  1. Windows
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)
 

Attachments

  • A03E96CA-A483-4365-B0BB-0B85214E68A9.jpeg
    A03E96CA-A483-4365-B0BB-0B85214E68A9.jpeg
    55.4 KB · Views: 5

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
Like this?

VBA Code:
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:
 
Solution
Joined
May 20, 2021
Messages
26
Office Version
  1. 2016
Platform
  1. Windows
Like this?

VBA Code:
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:

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!
 

Forum statistics

Threads
1,137,347
Messages
5,680,959
Members
419,946
Latest member
Trickay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top