Forum Discussion
Formulas & Conditional Formatting
Hello all, first time asking for help here.
I need to set a formula in my conditional formatting that will change the text in a row within the selected cells if that row contains specific text. Example:
Names | Roles | Days of the Month, 1-31, body of table is tracking absences and why absent
Name A |Role 1 | blank, or letter code for why absent
Name B | Role 2 | blank, or letter code for why absent
and so on....
What I would like is for a formula conditional formatting that would change the text of those letter codes if the role in that same column contained Role 1. I'd then do a different color for role 2 and so on, if I could just figure out that initial setup.
I know I can do it line by line by using =IF(b2="Role 1", True, False) but with the sheer number of name/role combinations I'm needing to do it would take an obscene amount of time, really hoping I can speed it up with a formula that causes any row in the table to change if the same row contains that text.
Jkey1990 , it works as in attached, all rows with CCD role are with red font.
5 Replies
Jkey1990 ,
You don't need to do that line by line. Apply the rule with the formula
=$B2="Role 1"
to your entire range. Same for other formattings. In general, one color/format - one rule.
SergeiBaklan Not quite what I'm looking for unless I entered the suggestion the wrong way, which is entirely possible.
I'm going to copy/paste the text from the table I'm using to give a better idea. The total table includes 170+ (I'll be doing multiple) names and has drop-down boxes for a total 10 possible roles. I need the conditional formatting to change the text color (say, to red) of any text in a row that contains CCD. Then I would do an additional rule for Pushback (change to blue) and so on until each of the more important roles would change color when a letter code is entered into a row that contains that role.
When I tried entering the suggestion it changed all of the letter codes into the same color whenever the role was selected in the role column.
(Some of it was cut off in order to post, the important bits are in the right spot though.)
Month Dates of Absence Year Name Role Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Employee 1 CCD V V V V V C C Employee 2 Pushback c S S I S V V V Jkey1990 , it works as in attached, all rows with CCD role are with red font.
