When entries have no corresponding entry IN RANGE indicated by conditional format

[ozbeachbum]

When entries in D9:13 have no corresponding entry F9:13 indicated by conditional format B7

if P & date.xlsx
	B	C	D	E	F	G	H
2	Looking for a formula to be used in Conditional Formatting
3	Where Ever "D" has the Letter P and "H" has NO DATE
4	Result will be Indicated in "B7"
5
6
7			Ind		Amount		Date
8
9			P		$ 1.00		Mon 01-Jul-41
10
11			P		$ 2.00
12
13					$ -
14
15
P NO DATE

 

[etaf]

just to confirm
you want B7 to show whenever
D9 to D13 - has a P or H
AND
No corresponding date in H9 to H13

What about F ???

=(COUNTIFS( $D$9:$D$13, "P", $H$9:$H$13 , "") + COUNTIFS( $D$9:$D$13, "H", $H$9:$H$13 , ""))>0

This will count if there is a P and blank date -
And also Add a count if there is an H and blank date

if blank dates then highlight for P and H

Book1
	A	B	C	D	E	F	G	H	I	J
1
2		Looking for a formula to be used in Conditional Formatting
3		Where Ever "D" has the Letter P and "H" has NO DATE
4		Result will be Indicated in "B7"
5
6										TEST
7				Ind		Amount		Date		TRUE
8
9				P		1		7/1/41
10
11				P		2		7/1/41
12
13				H		0
14
15
Sheet1

Cell Formulas
Range	Formula
J7	J7	=(COUNTIFS( $D$9:$D$13, "P", $H$9:$H$13, "") + COUNTIFS( $D$9:$D$13, "H", $H$9:$H$13, ""))>0

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B7	Expression	=(COUNTIFS( $D$9:$D$13, "P", $H$9:$H$13, "") + COUNTIFS( $D$9:$D$13, "H", $H$9:$H$13, ""))>0	text	YES

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

Highlight applicable range >>
B7 - 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:
=(COUNTIFS( $D$9:$D$13, "P", $H$9:$H$13 , "") + COUNTIFS( $D$9:$D$13, "H", $H$9:$H$13 , ""))>0

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

 

[Jeffrey Mahoney]

Please try this. The VBA code needs to be in a standard module.

Book7
	B	C	D	E	F	G	H
2	Looking for a formula to be used in Conditional Formatting
3	Where Ever "D" has the Letter P and "H" has NO DATE
4	Result will be Indicated in "B7"
5
6
7			Ind		Amount		Date
8
9			P		1		7/1/2041
10
11			P		2
12
13					0
14
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D8:D22	Expression	=AND($D8="P",issdate($H8)=FALSE)	text	NO

Function IssDate(rng As Variant) As Boolean
  Application.Volatile
  
  IssDate = IsDate(rng)

End Function

 

[ozbeachbum]

just to confirm
you want B7 to show whenever
D9 to D13 - has a P or H
AND
No corresponding date in H9 to H13

What about F ???

=(COUNTIFS( $D$9:$D$13, "P", $H$9:$H$13 , "") + COUNTIFS( $D$9:$D$13, "H", $H$9:$H$13 , ""))>0

This will count if there is a P and blank date -
And also Add a count if there is an H and blank date

if blank dates then highlight for P and H

Book1
	A	B	C	D	E	F	G	H	I	J
1
2		Looking for a formula to be used in Conditional Formatting
3		Where Ever "D" has the Letter P and "H" has NO DATE
4		Result will be Indicated in "B7"
5
6										TEST
7				Ind		Amount		Date		TRUE
8
9				P		1		7/1/41
10
11				P		2		7/1/41
12
13				H		0
14
15
Sheet1

Cell Formulas
Range	Formula
J7	J7	=(COUNTIFS( $D$9:$D$13, "P", $H$9:$H$13, "") + COUNTIFS( $D$9:$D$13, "H", $H$9:$H$13, ""))>0

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B7	Expression	=(COUNTIFS( $D$9:$D$13, "P", $H$9:$H$13, "") + COUNTIFS( $D$9:$D$13, "H", $H$9:$H$13, ""))>0	text	YES

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

Highlight applicable range >>
B7 - 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:
=(COUNTIFS( $D$9:$D$13, "P", $H$9:$H$13 , "") + COUNTIFS( $D$9:$D$13, "H", $H$9:$H$13 , ""))>0

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

just to confirm
you want B7 to show whenever
D9 to D13 - has a P or H
AND
No corresponding date in H9 to H13

H was just the column letter
D9 to D13 - has a P
AND
No corresponding date in H9 to H13

Thanks I just removed the + COUNTIFS( $D$9:$D$13, "H", $H$9:$H$13 , "") from your formula and it does exactly what I wanted it to do.

 

[ozbeachbum]

Please try this. The VBA code needs to be in a standard module.

Book7
	B	C	D	E	F	G	H
2	Looking for a formula to be used in Conditional Formatting
3	Where Ever "D" has the Letter P and "H" has NO DATE
4	Result will be Indicated in "B7"
5
6
7			Ind		Amount		Date
8
9			P		1		7/1/2041
10
11			P		2
12
13					0
14
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D8:D22	Expression	=AND($D8="P",issdate($H8)=FALSE)	text	NO

Function IssDate(rng As Variant) As Boolean
  Application.Volatile
 
  IssDate = IsDate(rng)

End Function

Thanks for taking the time to reply, however I need it to cover a range, so went with etaf's formula.

 

[etaf]

you are welcome
I miss read the text to say P & H rather than H column dah!!! - I see it now