Apply cell coloring from "conditional formatted" numerical cells to associated cells

[tbbhatna]

My application is fantasy football.

I rank football players (1-100), and am able to use conditional formatting on the ranking numbers to achieve a green-white-red spectrum from best to worst. How do I apply the same color for each ranking to the cell containing the name of the player, that is right next to the rank cell (see image)?

I would like to be able to show the rosters of all of the managers in my league. and have each of the players names be shaded based on their ranking, in order to visually appreciate which teams are weaker/stronger at various positions.

I am NOT proficient in VBA, and have mainly only used 'record macros' to do anything more complex than the ribbon tool abilities.

Thanks!

 

[MARK858]

How do I apply the same color for each ranking to the cell containing the name of the player, that is right next to the rank cell (see image)?

Just extend your "Applies to " range in your conditional formatting.

 

[tbbhatna]

Just extend your "Applies to " range in your conditional formatting.

Thanks for the quick response, but by extending the "Applies To" region for my 'column S' rule, to include the corresponding cells in column R, the cells in column R still do not take on the color of the cells in column S.

Am I not executing your suggestion correctly?

Thanks

 

[MARK858]

Post your current data using the boards XL2BB addin (click the XL2BB icon in the reply window it is a link) so that we can copy your data and see the conditional format settings.
Make sure that you select the Conditional formatting checkbox when the pop-up below appears.

 

[tbbhatna]

Post your current data using the boards XL2BB addin (click the XL2BB icon in the reply window it is a link) so that we can copy your data and see the conditional format settings.
Make sure that you select the Conditional formatting checkbox when the pop-up below appears.

View attachment 17174

Thank you for your patient help and support. Here is the data, with conditional formatting, as captured by the XL2BB add-in.

LXG Updated Rosters w Ranks.xlsx
	R	S
2	L. Jackson (3)	1
3	P. Mahomes (4)	2
4	D. Prescott (3)	3
5	R. Wilson (2)	4
6	K. Murray (NC)	5
7	D. Watson (3)	6
8	J. Allen (4)	7
9	M. Ryan (2)	8
10	C. Wentz (4)	9
11	D. Brees (1)	10
12	T. Brady (1)	11
13	A. Rodgers (3)	12
14	M. Stafford (1)	13
15	D. Jones (NC)	14
16	B. Mayfield (4)	15
17	R. Tannehill (NC)	16
18	B. Roethlisberger (2)	17
19	J. Goff (2)	18
20	J. Burrow (R)	19
21	K. Cousins (2)	20
22	J. Garoppolo (3)	21
23	P. Rivers (2)	22
24	G. Minshew (NC)	23
25	D. Lock (NC)	24
26	T. Bridgewater (NC)	25
27	S. Darnold (3)	26
28	D. Carr (2)	27
29	D. Haskins (NC)	28
30	T.Taylor (NC)	29
31	R. Fitzpatrick (NC)	30
32	N. Foles (1)	31
33	J. Stidham (NC)	32
34	T. Tagovailoa (R)	33
35	J. Herbert (R)	34
36	M. Trubisky (6)	35
37	J. Winston (NC)	36
38	C. Newton (3)	37
39	M. Mariota (NC)	38
40	T. Hill (NC)	39
41	A. Dalton (1)	40
LXG Summary

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
R2:S41	Other Type	Color scale		NO
R2:R41	Other Type	Color scale		NO

 

[MARK858]

Ok, you appear not to be able to just extend the apply range with Color scale so try the macro below

Sub colorAdja()
    Dim xCll As Range
    For Each xCll In Range("R2:R" & Range("R" & Rows.Count).End(xlUp).Row)
        xCll.Interior.Color = xCll.Offset(, 1).DisplayFormat.Interior.Color
    Next
End Sub

 

[tbbhatna]

Thank you for that bit of code! It works well, and since the new colored cell actually has the color as an attribute, I can copy its formatting to be used elsewhere as well.

Thank you!

 

[MARK858]

You're welcome. Just remember that you will need to rerun the macro if you change the data in column S.

 

[MARK858]

Sorry, should also have said welcome to the board