Conditional formatting formula with 2 criterias.

[strongman86]

Hi all,

Looking for formula to use in conditional formatting to colour cells if 2 criterias are met. Conditional formatting will be applied to column D. Numbers on column D will be compared with numbers in column B. For example: If D1 = B1 & if C1 = "PM" then highlight D1 cell, if C = AM then don't colour. Also if any cell in D is 0 then colour only if adjcent C cell is "AM". End result should be D1-highlighted,D2-NOT highlighted, D3-NOT highlighted,D4-highlighted,D5-highlighted,D6-NOT highlighted.
Thanks.

	B	C	D
1	5	PM	5
2	6	AM	0
3	1	AM	0
4	2	PM	2
5	3	PM	3
6	2	PM	0

 

[etaf]

not sure i follow

Also if any cell in D is 0 then colour only if adjcent C cell is "AM".

as D2 =0 and C2 = AM - so highlight
as D3 =0 and C3 = AM - so highlight

=OR(AND($C1="PM",$D1=$B1),AND($C1="AM",$D1=0))

Book1
	A	B	C	D
1	1	5	PM	5
2	2	6	AM	0
3	3	1	AM	0
4	4	2	PM	2
5	5	3	PM	3
6	6	2	PM	0
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D1:D6	Expression	=OR(AND($C1="PM",$D1=$B1),AND($C1="AM",$D1=0))	text	NO

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

Highlight applicable range >>
D1:D100 - 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:
=OR(AND($C1="PM",$D1=$B1),AND($C1="AM",$D1=0))

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

 

[strongman86]

not sure i follow


as D2 =0 and C2 = AM - so highlight
as D3 =0 and C3 = AM - so highlight

=OR(AND($C1="PM",$D1=$B1),AND($C1="AM",$D1=0))

Book1
	A	B	C	D
1	1	5	PM	5
2	2	6	AM	0
3	3	1	AM	0
4	4	2	PM	2
5	5	3	PM	3
6	6	2	PM	0
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D1:D6	Expression	=OR(AND($C1="PM",$D1=$B1),AND($C1="AM",$D1=0))	text	NO

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

Highlight applicable range >>
D1:D100 - 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:
=OR(AND($C1="PM",$D1=$B1),AND($C1="AM",$D1=0))

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

Sorry copy paste curse :D I meant highlight for D2& D3. =OR(AND($C1="PM",$D1=$B1),AND($C1="AM",$D1=0)) works perfectly. Thank you very much.

 

[etaf]

you are welcome