Conditional formatting a range based on the value of a single cell inside of it

Question

I'm trying to format a range based on the value of a single cell inside.

So if I have a range like in this image:

I am coloring based on the teams. If I was to rearrange the teams, how can I use conditional formatting to maintain the colors? Without having a set of conditional formatting formulas for each and every cell?

Answer 1

Unfortunately, you will need a conditional formatting rule for each team (not cell, though), as you can only set one fill color per rule. Assuming you want to format based on the value of an entire cell rather than a substring, then in each of your rules, set the condition to "Custom formula is…" and then enter =($B1="Team 1") (replacing "Team 1" with the value you wish to color with a given rule).

Make sure you include the equals sign at the beginning, as that tells Sheets that you are entering a formula. Also be sure to include the dollar sign ($) before the letter of the column of teams—I assumed it is column B based on your image—so that it only looks at the value in that column when determining whether or not to apply the formatting. (Using the dollar sign there turns it into an absolute column reference.)