Automated ranking board

[Asw091]

[FONT=&quot][FONT=&quot]Hello guys, I hope you can help.[/FONT][/FONT]
[FONT=&quot][FONT=&quot][/FONT]
[/FONT]
[FONT=&quot][FONT=&quot]I have a sheet that basically has names and scores, the names are in cells B3-B10 and their scores are in cells Z3-Z10. This is part of a scoreboard with other bits in between them so everything needs to stay where it is, however I would like to put a live 1st - 8th below it that will rank them in order, however rather than it just saying what position they’re in next to their score, I was hoping it would re order their names along with their scores in position order. (Cells A3-A10 would be #1 - #8).[/FONT][/FONT]
[FONT=&quot][FONT=&quot][/FONT]
[/FONT]
[FONT=&quot][FONT=&quot]For example, Phil gets 10 points and Paul gets 5, the list would show

[/FONT][FONT=&quot]
[/FONT][/FONT]
[TABLE="width: 500"]
<tbody>[TR]
[TD]Rank[/TD]
[TD]Name[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD]#1[/TD]
[TD]Phil[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]#2[/TD]
[TD]Paul[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]
[FONT=&quot][FONT=&quot][/FONT]
[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot][FONT=&quot][/FONT]
[/FONT]
[FONT=&quot][FONT=&quot]However if Paul gets 10 more, as I update the scores in Z3-Z10 it would re order them and show

[/FONT][FONT=&quot]
[/FONT][/FONT]
[TABLE="width: 500"]
<tbody>[TR]
[TD]Rank[/TD]
[TD]Name[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD]#1[/TD]
[TD]Paul[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]#2[/TD]
[TD]Phil[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]
[FONT=&quot]
[/FONT]
[FONT=&quot][FONT=&quot][/FONT]
[/FONT]
[FONT=&quot][FONT=&quot]But for 8. Is this possible?

Thanks.[/FONT][/FONT]

 

[Logit]

.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next




    If Not Intersect(Target, Range("D:D")) Is Nothing Then
        Range("D2").Sort Key1:=Range("D2"), _
        Order1:=xlDescending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom
        Application.DisplayAlerts = True
    End If
End Sub

[TABLE="width: 271"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Col A[/TD]
[TD]Col B (hide)[/TD]
[TD]Col C[/TD]
[TD]Col D[/TD]
[/TR]
[TR]
[TD]Rank[/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD]#1[/TD]
[TD][/TD]
[TD]Phil[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]#2[/TD]
[TD][/TD]
[TD]Paul[/TD]
[TD]10
[/TD]
[/TR]
</tbody>[/TABLE]


If you design your sheet like the above, the macro, pasted in to the Sheet level module, will auto sort the entries
based on entries in Col D. It will leave Col A as is because Col B is empty. The macro will only sort Cols C & D.

Yes, there are other methods of accomplishing your goal .. this is just one of many.