Conditional format based on text in same row [Solved]

joshcamp 2 Posts Tuesday June 20, 2017Registration date June 20, 2017 Last seen - Jun 20, 2017 at 09:26 AM - Latest reply: Mazzaropi 1831 Posts Monday August 16, 2010Registration dateContributorStatus May 21, 2018 Last seen
- Jun 20, 2017 at 01:29 PM
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!



See more 

Your reply

3 replies

Mazzaropi 1831 Posts Monday August 16, 2010Registration dateContributorStatus May 21, 2018 Last seen - Jun 20, 2017 at 01:29 PM
0
Thank you
joshcamp, Good afternoon.

I'm glad I could help.

Have a nice day.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Respond to Mazzaropi
Mazzaropi 1831 Posts Monday August 16, 2010Registration dateContributorStatus May 21, 2018 Last seen - Jun 20, 2017 at 10:11 AM
-1
Thank you
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
joshcamp 2 Posts Tuesday June 20, 2017Registration date June 20, 2017 Last seen - Jun 20, 2017 at 12:26 PM
Thank you Marcílio, this worked perfectly!
Respond to Mazzaropi