Conditional Formatting over multiple cells

[surkdidat]

Hi again

In Columns I, L and O I have various drop down options (4 in total)

If these dropdowns are any combination of either "Approved" or "Not Applicable" I need to turn the row green

If the row contains one of the other values "Open" or "Escalated" (or blank) the line should remain free of being coloured in

Thanks

 

[etaf]

Sorry for this - another idea

out of interest what is in columns J K M N - perhaps we can do some sort of simple count of the range
we could use the full range
=(COUNTIF($I2:$O2,"approved")+COUNTIF($I2:$O2,"not applicable"))=3
as conditional formatting will accept that - as its NOT an array

Book8
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2									Approved			Approved			Approved
3									Approved			Approved			Not applicable
4									Approved			Not applicable			Approved
5									Not applicable			Approved			Approved
6									Approved			Not applicable			Not applicable
7									Not applicable			Approved			Not applicable
8									Not applicable			Not applicable			Approved
9									Not applicable			Not applicable			Not applicable
10									Approved						Approved
11									Approved			Approved			Not applicable
12									Approved			open			Approved
13									Not applicable			Approved			Approved
14									escaleted			Not applicable			Not applicable
15									Not applicable			Approved			Not applicable
16									Not applicable			expired			Approved
17									Not applicable			Not applicable			open
Sheet1 (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:O17	Expression	=(COUNTIF($I2:$O2,"approved")+COUNTIF($I2:$O2,"not applicable"))=3	text	NO

 

[surkdidat]

Sorry for this - another idea

out of interest what is in columns J K M N - perhaps we can do some sort of simple count of the range
we could use the full range
=(COUNTIF($I2:$O2,"approved")+COUNTIF($I2:$O2,"not applicable"))=3
as conditional formatting will accept that - as its NOT an array

Book8
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2									Approved			Approved			Approved
3									Approved			Approved			Not applicable
4									Approved			Not applicable			Approved
5									Not applicable			Approved			Approved
6									Approved			Not applicable			Not applicable
7									Not applicable			Approved			Not applicable
8									Not applicable			Not applicable			Approved
9									Not applicable			Not applicable			Not applicable
10									Approved						Approved
11									Approved			Approved			Not applicable
12									Approved			open			Approved
13									Not applicable			Approved			Approved
14									escaleted			Not applicable			Not applicable
15									Not applicable			Approved			Not applicable
16									Not applicable			expired			Approved
17									Not applicable			Not applicable			open
Sheet1 (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:O17	Expression	=(COUNTIF($I2:$O2,"approved")+COUNTIF($I2:$O2,"not applicable"))=3	text	NO

Dates received / updated etc for status'

 

[etaf]

Dates received / updated etc for status'

if there is NO chance of having the words Approved or non applicable in those columns , then easier to use the simple countif()
=(COUNTIF($I2:$O2,"approved")+COUNTIF($I2:$O2,"not applicable"))=3
No helper - cna be in cond frmt as not an array and simple formula