SOLVED

Formulas & Conditional Formatting

Copper Contributor

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. 

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.

 

@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 AbsenceYear
NameRoleTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTue   
  1234567891011121314151617181920212223242526272829 
Employee 1CCD  VVVV      V        C      C   
Employee 2Pushbackc   SS    I        S    VVV     

 

best response confirmed by Jkey1990 (Copper Contributor)
Solution

@Jkey1990 , it works as in attached, all rows with CCD role are with red font. 

@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.

@Jkey1990 , no problem, you are welcome

1 best response

Accepted Solutions
best response confirmed by Jkey1990 (Copper Contributor)
Solution

@Jkey1990 , it works as in attached, all rows with CCD role are with red font. 

View solution in original post