Conditional Formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-1638356%22%20slang%3D%22en-US%22%3EConditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1638356%22%20slang%3D%22en-US%22%3EHello%2C%20I%20have%20been%20tasked%20with%20creating%20a%20draft%20board%20for%20our%20fantasy%20football%20league%20and%20am%20running%20into%20a%20conditional%20formatting%20issue.%3CBR%20%2F%3E%3CBR%20%2F%3EHere%20is%20the%20sheet%3A%20%3CA%20href%3D%22https%3A%2F%2Fdocs.google.com%2Fspreadsheets%2Fd%2F1Jw6zb3spk_Tq-javfIU63OlwQl1wVkxzSXAP1dMUGXY%2Fedit%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.google.com%2Fspreadsheets%2Fd%2F1Jw6zb3spk_Tq-javfIU63OlwQl1wVkxzSXAP1dMUGXY%2Fedit%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EWhat%20I%20am%20looking%20to%20do%20is%20when%20a%20player%20is%20drafted%2C%20have%20all%20the%20associated%20cells%20become%20color%20coded%20by%20position.%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20for%20example%2C%20in%20round%20one%20team%20one%20selected%20a%20%E2%80%9CRB%E2%80%9D.%20How%20do%20I%20get%20cells%20B2%2C%20C2%20and%20C3%20to%20all%20fill%20with%20the%20color%20associated%20to%20%E2%80%9CRB%E2%80%9D%3CBR%20%2F%3E%3CBR%20%2F%3EI%20know%20how%20to%20set%20the%20conditional%20format%20so%20that%20the%20cell%20that%20contains%20%E2%80%9CRB%E2%80%9D%20will%20format%20to%20fill%20whichever%20color%20I%20would%20like%2C%20but%20don%E2%80%99t%20know%20how%20to%20get%20cells%20B2%20and%20C3%20to%20do%20the%20same.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you!%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1638356%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1638432%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1638432%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F718125%22%20target%3D%22_blank%22%3E%40atravalgia%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20this%20range%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20778px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216755i1A0C1BC172B8AEFE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewhich%20starts%20from%20B2%2C%20you%20may%20apply%20the%20rule%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20421px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216756i1CF45CFA3540CA57%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewith%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D(B2%3D%22RB%22)%2B(B1%3D%22RB%22)%2B(A2%3D%22RB%22)%2B(A1%3D%22RB%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ewhich%20triggers%20formatting%20if%20current%20cell%2C%20or%20one%20cell%20up%20or%20left%2C%20or%20one%20cell%20left%20and%20up%20meet%20condition.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1638533%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1638533%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20reply%2C%20I%20tried%20that%20and%20it%20did%20not%20seem%20to%20work.%20I%20also%20think%20I%20did%20not%20explain%20myself%20too%20well%20the%20first%20time.%20I%20have%20update%20the%20above%20sheet.%20If%20you%20look%20at%20the%20first%20box%2C%20I%20have%20cell%20C3%20conditionally%20formatted%20to%20fill%20red%20because%20cell%20C3%20reads%20%22RB.%22%20With%20Cells%20B2%20%26amp%3B%20B3%20being%20associated%20to%20cell%20C3%20and%20%22RB%22%20I%20would%20like%20for%20those%20two%20sells%20to%20also%20fill%20in%20red.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1638538%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1638538%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F718125%22%20target%3D%22_blank%22%3E%40atravalgia%3C%2FA%3E%26nbsp%3Bconditional%20formatting%20is%20done%20relative%20to%20the%20upper%20left%20corner%20of%20the%20range%20it%20is%20applied%20to.%26nbsp%3B%20so%20in%20your%20case%20if%20you%20use%20the%20range%20B2%3AC3%20and%20a%20formula%20%3D%24C%243%3D%22RB%22%20then%20all%20cells%20B2%3AC3%20will%20be%20formatted%20if%20cell%20C3%20%3D%20%22RB%22%20but%20that%20will%20only%20work%20for%20that%20range%20since%20you%20are%20fixing%20cell%20%24C%243%20absolutely.%26nbsp%3B%20Now%20I%20believe%20you%20have%20spaces%20above%20and%20below%20and%20cells%20to%20the%20right%20and%20left%20that%20in%20no%20case%20should%20have%20%22RB%22%20or%20other%20initials%20you%20will%20probably%20be%20looking%20for%20so%20i%20think%20this%20should%20work%20to%20color%20all%20%22boxes%22%20with%20the%20bottom%20right%20corner%20being%20%22RB%22%3A%3C%2FP%3E%3CP%3Erange%3A%26nbsp%3B%20B2%3AZ50%26nbsp%3B%3C%2FP%3E%3CP%3Eformula%3A%20%3D%20OR(B2%3D%22RB%22%2C%20C2%3D%22RB%22%2C%26nbsp%3B%20B3%3D%22RB%22%2C%20C3%3D%22RB%22)%3C%2FP%3E%3CP%3Eand%20format%20%3D%20red%3C%2FP%3E%3CP%3Ethen%20repeat%20for%20each%20other%20color%2Fposition%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1638542%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1638542%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F718125%22%20target%3D%22_blank%22%3E%40atravalgia%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBetter%20if%20you%20submit%20your%20file%20directly%20here%20attaching%20to%20the%20post%2C%20it'll%20be%20easier%20to%20illustrate%20how%20it%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor
Hello, I have been tasked with creating a draft board for our fantasy football league and am running into a conditional formatting issue.

Here is the sheet: https://docs.google.com/spreadsheets/d/1Jw6zb3spk_Tq-javfIU63OlwQl1wVkxzSXAP1dMUGXY/edit

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

@atravalgia 

To this range

image.png

which starts from B2, you may apply the rule

image.png

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.

@Sergei Baklan 

 

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.

@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 

@atravalgia 

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

@Sergei Baklan 

 

Attached!

@atravalgia 

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

image.png

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

@Sergei Baklan 

 

Yes!

 

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

@atravalgia 

Good. Steps are

- select and edit your existing rule

image.png

- 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

image.png

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

image.png

- repeat for other rules

@Sergei Baklan 

 

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

@atravalgia , you are welcome