Cell Name change based on another cell BG Colour

[PeterOz]

Hi
I have a workbook with 10 sheets of players. E>G Week1 Game1 Week1 Game 2 etc.
I have two questions.
1) In the current game I use the large formula to change the background colour of the 1st 2nd & 3rd place.
E.g Tim has the highest score and the cell colour is light blue.
If another player gets more points then Tim as the game progresses the Light blue cell BG colour will change to that player.
This all works well.
What I am trying for is to have another cell change to the name of the person as the BG colour changes.
The pic attached should help make the question clearer.

2) When I click on a cell it will highlight the row and the column.
So I do not have to have to press F9 after choosing a new cell so the row and column will change I have on the first sheet Week 1 Game 1 VBA code
Application.Calculate.
Is it possible to have that one code work across all 10 sheets or do I have to add the Application.Calculate code to each individual sheet.

Cheers
Peteroz

 

[Muhammad_Usman]

Is this you want ??

Receip.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1
2																											Tim
3																											Chirs
4																											Adnan
5	Name														Score												Van
6	Tim														22												UB
7	Van														5												BJ
8	Adnan														7
9	Chirs														11
10	BJ														1
11	UB														2
12
13
Sheet15

Cell Formulas
Range	Formula
AA2:AA7	AA2	=IFNA(INDEX($A$6:$A$15,MATCH(LARGE($O$6:$O$15,ROW(A1)),$O$6:$O$15,0)),"")

 

[PeterOz]

Thank you Muhammad.
This works perfect for 1st place.
Can it be adapted to work for for 2nd and 3rd place?
Cheers
PeterOz

 

[Muhammad_Usman]

Thank you Muhammad.
This works perfect for 1st place.
Can it be adapted to work for for 2nd and 3rd place?
Cheers
PeterOz

i THINK IT RANK ALL PLAYER FROM TOP TO BOTTOM SCORE WISE

 

[Fluff]

How about

=TAKE(SORTBY(B6:B100,O6:O100,-1),3)

 

[PeterOz]

Hi Muhammad.
You are correct it does work.
I am sorry for the confusion.

Hi Fluff
I tried your formula and it give me a name error.

Do either of you have an idea about Question 2?
I can just put the code to each worksheet if need.
Thank you both for your help
Cheers
PeterOz

 

[noveske]

Do either of you have an idea about Question 2?
I can just put the code to each worksheet if need.
Thank you both for your help
Cheers
PeterOz

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Application.EnableEvents = False


Application.Calculate.CODE HERE

    Application.EnableEvents = True

End Sub

 

[PeterOz]

Hi Muhammed.
I hope I am not being a bother.
In the spreadsheet is a sheet report that shows the names an a total of each game and a grand total.
I wrongly assumed that if I put your formula next to the game formula it would show the current overall winner.
I added the sheet name Report and changed the cell reference.
By the result I get this is not correct.
is it possible to also show the first 3 places from the running total?
I put this in. IFNA(INDEX(Report!$A$5:$A$15,MATCH(LARGE(Report!$B$6:$B$15,ROW(B1)),Report!$B$6:$B$15,0)),"")
Cheers
PeterOz

 

[PeterOz]

Hi Noveske
Thanks for your input.
I tried the vba code and no luck.
I then tried putting it into a module and still no luck
Cheers
PeterOz

 

[Fluff]

Hi Fluff
I tried your formula and it give me a name error.

Sounds like you don't have the latest updates, another option is

=INDEX(SORTBY(B6:B100,O6:O100,-1),SEQUENCE(3))