May 03 2019 11:05 AM
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.
May 03 2019 12:39 PM
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.
May 03 2019 12:58 PM
@Sergei Baklan 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 |
May 03 2019 01:27 PM
Solution@Jkey1990 , it works as in attached, all rows with CCD role are with red font.
May 03 2019 03:48 PM
@Sergei Baklan Oh I feel like an idiot. I didn't set the "applies to" correctly. Thank you for the example so I was able to see where I was messing up. It irks me that it was so simple, but I am so glad to be able to get closer to finishing this. Thank you, sir.
May 03 2019 01:27 PM
Solution@Jkey1990 , it works as in attached, all rows with CCD role are with red font.