VBA Range Code

[kipper19]

Hi all,
Hoping someone can help with a VBA code to achieve this outcome, I have a spreadsheet and lets say in column C I have a list of values, in each row ie 12.55, 11.65, 13.65, 16.65, 10.65, I could have up to 100 rows of data.
I would like the code to find the highest value of all the numbers in Column C which in the example would be 16.65 and in Column D give it a score of 5, the 2nd highest rated number ie 13.65 would get 4, the 3rd 12.55 would get 3 points, the 2nd, 2 points and the 5th highest would get a score of 1, all others would get 0

 

[HongRu]

How about the formula ?
Fill below in D1 or your first data

=IF(RANK(C1,$C$1:$C$100)<=5,6-RANK(C1,$C$1:$C$100),"")

copy this to D2:D100

 

[kipper19]

Thanks HongRu, I'll test it out