Conditional Formatting - top row

[gtd526]

Hello,
Looking to Highlight the Top Row based on the MAX value of Row 9.
Top Row and Row 9 = Y1:CN1 and Y9:CN9.
Thank you.

Wagers.xlsm
	BK	BL	BM	BN	BO	BP	BQ	BR
1	M-R:T-Y:Z-W (2)		R:T-X:Z-W (1)		R:T-Y:Z-W (32)		R:T-X:Z (32)
2	W:	0	W:	2	W:	0	W:	0
3	L:	0	L:	0	L:	1	L:	1
4	Psh:	0	Psh:	0	Psh:	0	Psh:	0
5			100%		0%		0%
6	0		2		1		1
7	$0.00		$9.10		$0.00		$0.00
8	$0.00		$0.00		$5.00		$5.00
9	$0.00		$9.10		-$5.00		-$5.00
NBA

Cell Formulas
Range	Formula
BK1	BK1	="M-R:T-Y:Z-W"&" ("&RANK(BK9,$Y$9:$CN$9)&")"
BM1	BM1	="R:T-X:Z-W"&" ("&RANK(BM9,$Y$9:$CN$9)&")"
BO1	BO1	="R:T-Y:Z-W"&" ("&RANK(BO9,$Y$9:$CN$9)&")"
BQ1	BQ1	="R:T-X:Z"&" ("&RANK(BQ9,$Y$9:$CN$9)&")"
BL2	BL2	=COUNTIFS($L$3:$L$1210,"M-R:T-Y:Z-W",$A$3:$A$1210,"W")+(BL4*0.5)
BL3	BL3	=COUNTIFS($L$3:$L$1210,"M-R:T-Y:Z-W",$A$3:$A$1210,"L")+(BL4*0.5)
BL4	BL4	=COUNTIFS($L$3:$L$1210,"M-R:T-Y:Z-W",$A$3:$A$1210,"psh")
BN2	BN2	=COUNTIFS($L$3:$L$1210,"R:T-X:Z-W",$A$3:$A$1210,"W")+(BN4*0.5)
BN3	BN3	=COUNTIFS($L$3:$L$1210,"R:T-X:Z-W",$A$3:$A$1210,"L")+(BN4*0.5)
BN4	BN4	=COUNTIFS($L$3:$L$1210,"R:T-X:Z-W",$A$3:$A$1210,"psh")
BP2	BP2	=COUNTIFS($L$3:$L$1210,"R:T-Y:Z-W",$A$3:$A$1210,"W")+(BP4*0.5)
BP3	BP3	=COUNTIFS($L$3:$L$1210,"R:T-Y:Z-W",$A$3:$A$1210,"L")+(BP4*0.5)
BP4	BP4	=COUNTIFS($L$3:$L$1210,"R:T-Y:Z-W",$A$3:$A$1210,"psh")
BR2	BR2	=COUNTIFS($L$3:$L$1210,"R:T-X:Z",$A$3:$A$1210,"W")+(BR4*0.5)
BR3	BR3	=COUNTIFS($L$3:$L$1210,"R:T-X:Z",$A$3:$A$1210,"L")+(BR4*0.5)
BR4	BR4	=COUNTIFS($L$3:$L$1210,"R:T-X:Z",$A$3:$A$1210,"psh")
BK5,BQ5,BO5,BM5	BK5	=IFERROR(BL2/(BL2+BL3),"")
BK6,BQ6,BO6,BM6	BK6	=IFERROR(BL2+BL3,"")
BK7,BQ7,BO7,BM7	BK7	=BL2*4.55
BK8,BQ8,BO8,BM8	BK8	=BL3*5
BK9,BQ9,BO9,BM9	BK9	=BK7-BK8

 

[jasonb75]

=Y$9=MAX($Y$9:$CN$9) applied to Y1:CN1

 

[gtd526]

=Y$9=MAX($Y$9:$CN$9) applied to Y1:CN1

Thank you. Can I use Large 2,3,4 with this formula?

 

[jasonb75]

Large, Small, Min, Max, probably a few others as well.

 

[gtd526]

Large, Small, Min, Max, probably a few others as well.

It seems its only accepting the MAX and #2, i'm also looking for 3 & 4.

Wagers.xlsm
	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF
1	R:T-X:Z-W (1)		R:T-Y:Z-W (32)		R:T-X:Z (32)		R:T-Y:Z (2)		R:T-M-Q-W (2)		R:T-Q-W (2)		R:T-M (2)		R:T-Q (2)		R:T-W (31)		R:T (2)
2	W:	2	W:	0	W:	0	W:	0	W:	0	W:	0	W:	0	W:	0	W:	1.5	W:	0
3	L:	0	L:	1	L:	1	L:	0	L:	0	L:	0	L:	0	L:	0	L:	1.5	L:	0
4	Psh:	0	Psh:	0	Psh:	0	Psh:	0	Psh:	0	Psh:	0	Psh:	0	Psh:	0	Psh:	1	Psh:	0
5	100%		0%		0%												50%
6	2		1		1		0		0		0		0		0		3		0
7	$9.10		$0.00		$0.00		$0.00		$0.00		$0.00		$0.00		$0.00		$6.83		$0.00
8	$0.00		$5.00		$5.00		$0.00		$0.00		$0.00		$0.00		$0.00		$7.50		$0.00
9	$9.10		-$5.00		-$5.00		$0.00		$0.00		$0.00		$0.00		$0.00		-$0.68		$0.00
NBA

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
BM1:CF1	Expression	=BM$9=MAX($BM$9:$CF$9)	text	NO
BM1:CF1	Expression	=BM$9=LARGE($BM$9:$CF$9,2)	text	NO
BM1:CF1	Expression	=BM$9=LARGE($BM$9:$CF$9,4)	text	NO
BM1:CF1	Expression	=BM$9=LARGE($BM$9:$CF$9,3)	text	NO

 

[jasonb75]

Technically, that is correct. There are 6 cells with $0.00, so that covers 2nd to 7th largest.
-$0.68 is the 7th largest, -$5.00 are the 8th and 9th largest.

To apply the same ranking to equal results needs some additional functions, usually countif, although I'm not sure how well it will work with your layout. The merged columns are more than likely to make it fail. I'll have a look at it in detail when I get home later.

 

[gtd526]

Technically, that is correct. There are 6 cells with $0.00, so that covers 2nd to 7th largest.
-$0.68 is the 7th largest, -$5.00 are the 8th and 9th largest.

To apply the same ranking to equal results needs some additional functions, usually countif, although I'm not sure how well it will work with your layout. The merged columns are more than likely to make it fail. I'll have a look at it in detail when I get home later.

We're good. I changed the order of Cond Form and as the ranges are populated, it comes together.
It's better as I rid of the $0.00's (since it will populate many ranks, as stated above). Thank you.