# Conditional Formatting

Occasional Contributor

# Conditional Formatting

Hello, I have been tasked with creating a draft board for our fantasy football league and am running into a conditional formatting issue.

What I am looking to do is when a player is drafted, have all the associated cells become color coded by position.

So for example, in round one team one selected a “RB”. How do I get cells B2, C2 and C3 to all fill with the color associated to “RB”

I know how to set the conditional format so that the cell that contains “RB” will format to fill whichever color I would like, but don’t know how to get cells B2 and C3 to do the same.

Thank you!
10 Replies

# Re: Conditional Formatting

To this range

which starts from B2, you may apply the rule

with formula

``=(B2="RB")+(B1="RB")+(A2="RB")+(A1="RB")``

which triggers formatting if current cell, or one cell up or left, or one cell left and up meet condition.

# Re: Conditional Formatting

Thank you for the reply, I tried that and it did not seem to work. I also think I did not explain myself too well the first time. I have update the above sheet. If you look at the first box, I have cell C3 conditionally formatted to fill red because cell C3 reads "RB." With Cells B2 & B3 being associated to cell C3 and "RB" I would like for those two sells to also fill in red.

# Re: Conditional Formatting

@atravalgia conditional formatting is done relative to the upper left corner of the range it is applied to.  so in your case if you use the range B2:C3 and a formula =\$C\$3="RB" then all cells B2:C3 will be formatted if cell C3 = "RB" but that will only work for that range since you are fixing cell \$C\$3 absolutely.  Now I believe you have spaces above and below and cells to the right and left that in no case should have "RB" or other initials you will probably be looking for so i think this should work to color all "boxes" with the bottom right corner being "RB":

range:  B2:Z50

formula: = OR(B2="RB", C2="RB",  B3="RB", C3="RB")

and format = red

then repeat for each other color/position

# Re: Conditional Formatting

Better if you submit your file directly here attaching to the post, it'll be easier to illustrate how it work.

Attached!

# Re: Conditional Formatting

Thank you. I updated the rule for RB, please check if that what you'd like to have.

If so other rules could be updated with the same logic.

# Re: Conditional Formatting

Yes!

This exactly what I am looking for. What steps do I need to take?

# Re: Conditional Formatting

Good. Steps are

- select and edit your existing rule

- change Rule type on Use a formula... and copy / paste formula from rule I updated

- change in formula RB om proper text. To edit the formula in a bar press F2

- keep format as it is, Ok and in next window Apply

- repeat for other rules

# Re: Conditional Formatting

Got it to work, thank you so much for your help.

# Re: Conditional Formatting

@atravalgia , you are welcome