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]

select the row you want to colour
Then use a formula
=AND(OR($I2="Approved",$L2="approved",$O2="approved"),OR($I2="Not applicable",$L2="Not applicable",$O2="Not applicable"))

Book8
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2		TRUE							approved			not applicable			approved
3									not applicable						approved
4												approved			approved
Sheet1

Cell Formulas
Range	Formula
B2	B2	=AND(OR($I2="Approved",$L2="approved",$O2="approved"),OR($I2="Not applicable",$L2="Not applicable",$O2="Not applicable"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:R5	Expression	=AND(OR($I2="Approved",$L2="approved",$O2="approved"),OR($I2="Not applicable",$L2="Not applicable",$O2="Not applicable"))	text	YES

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
A2:AA100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=AND(OR($I2="Approved",$L2="approved",$O2="approved"),OR($I2="Not applicable",$L2="Not applicable",$O2="Not applicable"))

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

 

[surkdidat]

select the row you want to colour
Then use a formula
=AND(OR($I2="Approved",$L2="approved",$O2="approved"),OR($I2="Not applicable",$L2="Not applicable",$O2="Not applicable"))

Book8
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2		TRUE							approved			not applicable			approved
3									not applicable						approved
4												approved			approved
Sheet1

Cell Formulas
Range	Formula
B2	B2	=AND(OR($I2="Approved",$L2="approved",$O2="approved"),OR($I2="Not applicable",$L2="Not applicable",$O2="Not applicable"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:R5	Expression	=AND(OR($I2="Approved",$L2="approved",$O2="approved"),OR($I2="Not applicable",$L2="Not applicable",$O2="Not applicable"))	text	YES

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
A2:AA100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=AND(OR($I2="Approved",$L2="approved",$O2="approved"),OR($I2="Not applicable",$L2="Not applicable",$O2="Not applicable"))

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

Superb - thank you - that has saved me a lot of frustrating as was near the solution, but not quite!

 

[etaf]

you are welcome

 

[surkdidat]

Hi

Sorry, cannot edit - but I have one row that in I L and O the "answers" are all "Approved" but it is not turning the light green. (Lines with a combination appear to work.

select the row you want to colour
Then use a formula
=AND(OR($I2="Approved",$L2="approved",$O2="approved"),OR($I2="Not applicable",$L2="Not applicable",$O2="Not applicable"))

Book8
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2		TRUE							approved			not applicable			approved
3									not applicable						approved
4												approved			approved
Sheet1

Cell Formulas
Range	Formula
B2	B2	=AND(OR($I2="Approved",$L2="approved",$O2="approved"),OR($I2="Not applicable",$L2="Not applicable",$O2="Not applicable"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:R5	Expression	=AND(OR($I2="Approved",$L2="approved",$O2="approved"),OR($I2="Not applicable",$L2="Not applicable",$O2="Not applicable"))	text	YES

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
A2:AA100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=AND(OR($I2="Approved",$L2="approved",$O2="approved"),OR($I2="Not applicable",$L2="Not applicable",$O2="Not applicable"))

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

 

[etaf]

Ignore - need to rethink

are OK, i thought it had to have both
Approved and Not applicable

So if it just has 1 approved - then is that green
or 1 not applicable

This will turn green if any cell has approved OR not applicable
=OR($I2="Approved",$L2="approved",$O2="approved",$I2="Not applicable",$L2="Not applicable",$O2="Not applicable")

BUT if you have any cells that are
"Open" or "Escalated" or blank then its not green

let me rethink that

 

[surkdidat]

are OK, i thought it had to have both
Approved and Not applicable

So if it just has 1 approved - then is that green
or 1 not applicable

So it can be ANY combination of Approved or Not applicable (think I have covered all scenarios )

Approved	Approved	Approved
Approved	Approved	Not applicable
Approved	Not applicable	Approved
Not applicable	Approved	Approved
Approved	Not applicable	Not applicable
Not applicable	Approved	Not applicable
Not applicable	Not applicable	Approved
Not applicable	Not applicable	Not applicable

 

[etaf]

so all the above are GREEN - anything else if not coloured

 

[surkdidat]

so all the above are GREEN - anything else if not coloured

Yes - (albeit I have missed a combination of those two values) - there maybe a possibility of a 3rd option of "Expired" to be added at a later date, but was just hoping I could add an extra OR statement.

 

[etaf]

sorry, more complicated then first read - my fault

What are the values

Blank
Open
Escalated
Expired
Approved
Non Applicable

green ONLY if ALL 3 cells have a combination of
Approved
OR
Non Applicable
Everything else is blank

so if we count the contents of a cell - then it should= 3 as, we have to have all 3 cells populated with those 2 words
i can do with a helper , using an array formula - which is not allowed in conditional formatting, so it will be much longer
maybe a sumproduct
BUT can we just try with a helper column and SEE if that does give the result you need and if so look at not using a helper later
OR another member may read and answer

The formula i'm looking at is
=SUM((COUNTIF($I13,{"Approved","not applicable"})))+SUM((COUNTIF(L13,{"Approved","not applicable"})))+SUM((COUNTIF($O13,{"Approved","not applicable"})))
and so that must be 3 to be green

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

Cell Formulas
Range	Formula
R2:R17	R2	=SUM((COUNTIF($I2,{"Approved","not applicable"})))+SUM((COUNTIF(L2,{"Approved","not applicable"})))+SUM((COUNTIF($O2,{"Approved","not applicable"})))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:R17	Expression	=$R2=3	text	NO