Whilst these replies have flooded in I thought I might turn my novice
hand to the problem and came up with the following, much messier
solution for what it's worth...

It assumes that you have 20 numbers in cells A1:A20 on a sheet called
"Sheet1".

Option Explicit

Private Sub CommandButton1_Click()
Dim i As Integer
Dim theRange As Range
i = 1
Set theRange = Sheets("Sheet1").Range("A1:A20")
'Takes any existing colour formats away
theRange.Select
Selection.Interior.ColorIndex = xlNone

With Application.WorksheetFunction

'loop to check each number in the range with the RANK function and
colour accordingly

While i < 20
If .Rank(Cells(i, 1).Value, theRange, 0) = 1 Then
Cells(i, 1).Interior.ColorIndex = 3
End If

If .Rank(Cells(i, 1).Value, theRange, 0) = 2 Then
Cells(i, 1).Interior.ColorIndex = 6
End If

If .Rank(Cells(i, 1).Value, theRange, 0) = 3 Then
Cells(i, 1).Interior.ColorIndex = 5
End If

If .Rank(Cells(i, 1).Value, theRange, 0) = 4 Then
Cells(i, 1).Interior.ColorIndex = 16
End If

If .Rank(Cells(i, 1).Value, theRange, 0) = 5 Then
Cells(i, 1).Interior.ColorIndex = 10
End If

i = i + 1
Wend
End With

End Sub