Conditional format based on text in same row [Solved/Closed]

Report
Posts
2
Registration date
Tuesday June 20, 2017
Status
Member
Last seen
June 20, 2017
-
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 16, 2020
-
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!



2 replies

Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 16, 2020
135
joshcamp, Good afternoon.

I'm glad I could help.

Have a nice day.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 16, 2020
135
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
Posts
2
Registration date
Tuesday June 20, 2017
Status
Member
Last seen
June 20, 2017

Thank you Marcílio, this worked perfectly!