Conditional format based on text in same row Solved/Closed

Question

Hi all,

New to this forum.

I'm running Excel 2016 on Win7 -- my spreadsheet has results of inspections in columns E, G, and J. The possible values are Pass, Fail, and Inconclusive. I have these columns conditional-formatted so "Pass" automatically shades green, "Fail" red, and "Inconclusive" yellow.

I would like to have column C automatically change to show the "status" of each row as follows: if E, G, or J in the same row show Pass, C would shade green and automatically add the text "Pass". If there is no "Pass" in any of these columns, C would shade red and add the text "Fail". Unlike the other columns, I would like to not have to manually update this column.

I'm not sure if this would be approached as a conditional format, a simple IF function, or both, and it's left me scratching my head.

Thank you very much for your help!



System Configuration: Windows / Chrome 58.0.3029.110

Mazzaropi · Answer

joshcamp, good morning. "...I'm not sure if this would be approached as a conditional format, a simple IF function, or both, ..." You'll need to use BOTH. Try to use: a) C1 --> =IF(OR(E1="Pass", G1="Pass", J1="Pass"), "Pass", IF(AND(E1<>"Pass", G1<>"Pass", J1<>"Pass"), "Fail", "" )) b) Conditional Format Select C column as desired Using formula Rule 1 --> =$C1="Pass" --> Format: Fill color GREEN Rule 2 --> =$C1="Fail" --> Format: Fill color RED OK Please, tell us if it worked as you desired. I hope it helps. -- Belo Horizonte, Brasil. Marcílio Lobão

Mazzaropi · Answer

joshcamp, Good afternoon. 

I'm glad I could help.

Have a nice day. 
 --
Belo Horizonte, Brasil.
Marcílio Lobão

Conditional format based on text in same row

2 responses

Similar discussions

Subscribe To Our Newsletter!