Conditional Formatting

[Patches01]

How do I conditional format a row with the following conditions. First cell has "0" , second cell has "0" , third cell has "N/A" , fourth cell has "N/A" and the fifth cell has "N/A".

Date_Of_Birth	Subs_Paid_Till	Member_Level	FIRST CELL	SECOND CELL	THIRD CELL	FOURTH CELL	FIFTH CELL
20/01/1941​	1/01/2100​		2580.5​	26.9​	2​	2​	#N/A​
8/09/1943​	1/01/2100​		329.7​	107.6​	3​	3​	3​
29/01/1946​	1/01/2100​		0.32​	0​	4​	4​	4​
21/12/1949​	31/12/2099​		75.7​	25.9​	#N/A​	5​	#N/A​
21/05/1934​	1/01/2100​		0​	0​	#N/A​	#N/A​	#N/A​
15/06/1947​	31/12/2099​		0​	0​	#N/A​	#N/A​	#N/A​
20/01/1946​	1/01/2100​		2560.7​	0​	#N/A​	#N/A​	#N/A​
27/09/1945​	1/01/2100​		0.9​	0​	9​	9​	9​
5/12/1943​	1/01/2100​		54.5​	53.8​	10​	10​	10​
1/01/1945​	1/01/2100​		60.4​	53.8​	12​	12​	12​

 

[Joe4]

For row 2, your Conditional Formatting Formula would look like (assuming "FIRST CELL" is column D, etc):

=AND($D2=0,$E2=0,ISNA($F2),ISNA($G2),ISNA($H2))

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Date_Of_Birth	Subs_Paid_Till	Member_Level	FIRST CELL	SECOND CELL	THIRD CELL	FOURTH CELL	FIFTH CELL
2	20/01/1941	01/01/2100		2580.5	26.9	2	2	#N/A
3	09/08/1943	01/01/2100		329.7	107.6	3	3	3
4	29/01/1946	01/01/2100		0.32	0	4	4	4
5	21/12/1949	31/12/2099		75.7	25.9	#N/A	5	#N/A
6	21/05/1934	01/01/2100		0	0	#N/A	#N/A	#N/A
7	15/06/1947	31/12/2099		0	0	#N/A	#N/A	#N/A
8	20/01/1946	01/01/2100		2560.7	0	#N/A	#N/A	#N/A
9	27/09/1945	01/01/2100		0.9	0	9	9	9
10	12/05/1943	01/01/2100		54.5	53.8	10	10	10
11	01/01/1945	01/01/2100		60.4	53.8	12	12	12
12
13
14
Master

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:H17	Expression	=AND(COUNTIFS($D2:$E2,0)=2,COUNTIFS($F2:$H2,"#N/A"))	text	NO

 

[Patches01]

=AND(COUNTIFS($D2:$E2,0)=2,COUNTIFS($F2:$H2,"#N/A"))

Which condition did you use?

 

[Joe4]

Which condition did you use?

 

[Patches01]

Doesn't seem to be working for me. Is not applying to all lines correctly.

 

[Fluff]

What range did you select before applying the rule?

 

[Joe4]

Doesn't seem to be working for me. Is not applying to all lines correctly.

The formula needs to be in agreement with the range that you selected to apply this to.
The best way to do this is to first select the entire range that you want to apply it to, then write the formula as it pertains to the FIRST cell in your selected range.
Excel is smart enough to adjust the formula for the other cells in your selected range.