Conditional Formatting - Multiple Criteria

[surkdidat]

I need a table to highlight the cells from C to J IF Cell D contains "Lee" and any cell from E to J Contains "Yes".

So for instance


.C.. | . D . |. E . |. F . |. G . |. H . |

Lee | YES | YES | YES | YES | YES | (All row should be highlighted)
--------------------------
Lee | YES | YES | | | | Cells C to E should be highlighted only in row)
--------------------------
Rik | | | | | | (Nothing should be highlighted)
--------------------------
Lee | YES | YES | YES | YES | | (Cells C to G should be highlighted)


Hope that makes sense!!
------------

 

[wideboydixon]

Not 100% clear. Which cells should be highlighted in this case?

Book1
	C	D	E	F	G	H	I	J
1	Lee	YES	YES	YES	YES	YES	YES
2	Lee	YES	YES
3	Rik	YES
4	Lee	YES		YES	NO	YES
5	Lee
Sheet1

WBD

 

[surkdidat]

Hi
Row 1 C, D,E,F,G,H,I
Row 2 C,D,E
Row 3 - (None)
Row 4 - C, D, F,H

(Cells D: J will only either be Yes or left blank, and all will be in a row, there wont be gaps between the Yeses)

Not 100% clear. Which cells should be highlighted in this case?

	C	D	E	F	G	H	I	J
Lee	YES	YES	YES	YES	YES	YES
Lee	YES	YES
Rik	YES
Lee	YES	YES	NO	YES
Lee

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

WBD

 

[wideboydixon]

OK. Highlight column C and add a new conditional format based on the following formula:

=AND($C1="Lee",$D1="YES")

Now highlight columns D:J and add another conditional format based on the following formula:

=AND($C1="Lee",D1="YES")

Note the use of the $ to anchor the columns and the fact that it's missing from the second part of the second formula. Make sure both conditions use the same format.

WBD

 

[surkdidat]

Works like a charm! Many thanks

OK. Highlight column C and add a new conditional format based on the following formula:

=AND($C1="Lee",$D1="YES")

Now highlight columns D:J and add another conditional format based on the following formula:

=AND($C1="Lee",D1="YES")

Note the use of the $ to anchor the columns and the fact that it's missing from the second part of the second formula. Make sure both conditions use the same format.

WBD