# Fill cell colour according to percentile.

#### XanderTheNotSoAwesome

##### New Member
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
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
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:

#### XanderTheNotSoAwesome

##### New Member
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!

Happy to help

Replies
1
Views
149
Replies
22
Views
277
Replies
0
Views
81
Replies
6
Views
291
Replies
11
Views
325

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?

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