Does it have to be VBA?
Formula solution

=IF(RANK(A1,A$1:A$7)>5,"",RANK(A1,A$1:A$7))
and copy down