Problems with formula for conditional formatting

[DarkoDeign2]

I want to highlight the rows A3:H3 if cell D3 is blank but D2 and D4 have text in them.
I have experimented with this formula: =AND(ISBLANK(D3); COUNTA(D$2:D2)>0; COUNTA(D4:D$1000)>0)
But some how this doesn't work as you can see below:

Book4
	A	B	C	D				H	I
2	Week	Date	Time slot	Topic
3	wk30	2023-07-26		BBQ Party					FALSE
4	wk30	2023-07-26		Pay electrical bills					FALSE
5	wk30	2023-07-26		Renew subrscription of newspaper					FALSE
6	wk30	2023-07-26		Get a hair cut					FALSE
7	wk30	2023-07-26		Shopping					FALSE
8	wk30	2023-07-26		Cooking					FALSE
9	:::::								TRUE
10	wk31	2023-08-02		Walk the dog					FALSE
11	:::::								FALSE
Agenda

Cell Formulas
Range	Formula
A3:A11	A3	=IFERROR(IF(ISBLANK($B3),":::::",ISOWEEKNUM($B3)),":::::")
B4:B11	B4	=IF(D4="","",LOOKUP(9^9,B$3:B3)+7*(B3=""))
I3:I11	I3	=AND(ISBLANK(D3), COUNTA(D$2:D2)>0, COUNTA(D4:D$1000)>0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:G1000	Expression	=AND(ISBLANK(D3); COUNTA(D$2:D2)>0; COUNTA(D4:D$1000)>0)	text	NO

But if I put the same formula in Column I (helper column) and change the conditional formating formula to: =$I3=TRUE it works as you can see below:

Book4
	A	B	C	D				H	I
2	Week	Date	Time slot	Topic
3	wk30	2023-07-26		BBQ Party					FALSE
4	wk30	2023-07-26		Pay electrical bills					FALSE
5	wk30	2023-07-26		Renew subrscription of newspaper					FALSE
6	wk30	2023-07-26		Get a hair cut					FALSE
7	wk30	2023-07-26		Shopping					FALSE
8	wk30	2023-07-26		Cooking					FALSE
9	:::::								TRUE
10	wk31	2023-08-02		Walk the dog					FALSE
11	:::::								FALSE
Agenda (2)

Cell Formulas
Range	Formula
A3:A11	A3	=IFERROR(IF(ISBLANK($B3),":::::",ISOWEEKNUM($B3)),":::::")
B4:B11	B4	=IF(D4="","",LOOKUP(9^9,B$3:B3)+7*(B3=""))
I3:I11	I3	=AND(ISBLANK(D3), COUNTA(D$2:D2)>0, COUNTA(D4:D$1000)>0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:G1000	Expression	=$I3=TRUE	text	NO

What am I doing wrong?

 

[Joe4]

I believe that you need to lock down your columns, but allow your rows to float, i.e.

=AND(ISBLANK($D3);$D2<>"";$D4<>"")

 

[DarkoDeign2]

Oh, I see. Now it is working as expected. Thanks for the help!

 

[Joe4]

You are welcome.

Take a look here for more information on Absolute, Relative, and Mixed cell references in Excel.

Relative and absolute cell reference: why use $ in Excel formula

Get the insight into Excel absolute and relative cell references, and find out why use the dollar sign ($) in an Excel formula.

www.ablebits.com