Conditional formatting using AND statement with Error

[rjplante]

I would like to have a cell that will use a multiple condition for cell formatting. I need the cell to check a cell at the top of the column and if the current cell is in error. I currently have the following in the conditional formatting formula field:

=AND(D$4 = "YES", C11=Error)

This does not result in the yellow cell fill formatting being applied to cell C11.

Is it possible to incorporate an AND statement that uses a standard formula along with an Error statement? If so how do I enter it in the conditional formatting formula field?

 

[Special-K99]

Different errors which set different flags and can be tested with different formulas.
ISERR()
ISERROR()
ISNA()
may be others, can't recall.

Use the appropriate one depending on which error is being set, e.g.

=AND(D$4="YES",ISNA(C11))

 

[Joe4]

Try:

=AND(D$4 = "YES", ISERROR(C11))

 

[rjplante]

I have tried all of these options from above:

ISERR()
ISERROR()
ISNA()

and none of the result in the cell C11 being highlighted. Will I have to use a second helper cell to do all of the complex calculations and then return a 1 or 0 for my results, which I can then use in the formula for conditional formatting?

I don't know the best way to proceed.

 

[Fluff]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Special-K99]

Maybe D4 is not equal to "YES"

In cases like this break it down

in a blank cell enter

=D$4="YES"

If it says TRUE then the fault lies with the "If error", if it says FALSE then D$4 is not equal to "YES"

 

[Special-K99]

What's the formula in C11 ?

 

[rjplante]

Checked cell d4 and value returned True. Formula in cell C11 is:

=IF(VLOOKUP($A11,'Pricing Table'!$B$5:$I$756,C$4,FALSE) = "","",VLOOKUP($A11,'Pricing Table'!$B$5:$I$756,C$4,FALSE))

Here is the section of the spreadsheet from XL2BB

Pricing Template 01-SEP-2021 - TEST.xltm
	A	B	C	D
4		Quantity	2	YES
6	Component	0	1
7	Test 1		$286,625
8	Test 2
9	ACCESSORIES	0	1
10	48" Wide spool		$28,270
11	96" Wide spool		$40,565
Pricing Sheet

Cell Formulas
Range	Formula
B6	B6	=SUM(B7:B8)
C7:C8,C10:C11	C7	=IF(VLOOKUP($A7,'Pricing Table'!$B$5:$I$756,C$4,FALSE) = "","",VLOOKUP($A7,'Pricing Table'!$B$5:$I$756,C$4,FALSE))
D7:D8,D10:D11	D7	=IF(ISERROR($B7*C7),"",IF(OR($A7="",$B7=""),"",$B7*C7))
B9	B9	=SUM(B10:B18)

Named Ranges
Name	Refers To	Cells
'Pricing Table'!Parts	='Pricing Table'!$B$5:$B$729	C10:C11, C7:C8
Parts	='Pricing Sheet'!$A$7:$A$692	C7:D7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A10:A18	Expression	=$BP10<>TRUE	text	NO
D4	Cell	contains a blank value	text	NO
C6	Expression	=MOD(COLUMN(),2)=0	text	NO
C9:P9	Expression	=MOD(COLUMN(),2)=0	text	NO
D4,F4,H4,J4,L4,N4,P4	Cell Value	="YES"	text	NO
D4,F4,H4,J4,L4,N4,P4	Cell Value	="NO"	text	NO
D301:P301,C294:P300,C275:P291,C273:P273,C252:P258,C238:P250,C229:P236,C226:P227,C222:P223,C219:P220,C215:P216,C212:P213,C210:P210,C206:P208,C201:P204,C196:P199,C191:P194,C189:P189,C186:P187,C182:P184,C179:P180,C176:P177,C169:P174,C161:P167,C158:P159	Expression	=MOD(COLUMN(),2)=0	text	NO
C9:P399	Expression	=AND(D$4="YES",ISNA(C14))	text	NO
C6:P8	Expression	=AND(D$4="YES",ISNA(C10))	text	NO

 

[Fluff]

None of those cells have an error in col C, hence nothing is highlighted. Although the C11 should reflect the 1st row in the applies to range.
Also if you want it to highlight the row you need to use

=AND($D$4="YES",ISNA($C6))

 

[rjplante]

Here is an updated section with the error in it.

Pricing Template 01-SEP-2021 - TEST.xltm
	A	B	C	D
4		Quantity	2	YES
6	Component	0	1
7	new entry		#N/A
8	Test 2
9	ACCESSORIES	0	1
10	48" Wide spool		$28,270
11	96" Wide spool		$40,565
Pricing Sheet

Cell Formulas
Range	Formula
B6	B6	=SUM(B7:B8)
C7:C8,C10:C11	C7	=IF(VLOOKUP($A7,'Pricing Table'!$B$5:$I$756,C$4,FALSE) = "","",VLOOKUP($A7,'Pricing Table'!$B$5:$I$756,C$4,FALSE))
D7:D8,D10:D11	D7	=IF(ISERROR($B7*C7),"",IF(OR($A7="",$B7=""),"",$B7*C7))
B9	B9	=SUM(B10:B18)

Named Ranges
Name	Refers To	Cells
'Pricing Table'!Parts	='Pricing Table'!$B$5:$B$729	C10:C11, C7:C8
Parts	='Pricing Sheet'!$A$7:$A$692	C7:D7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A10:A18	Expression	=$BP10<>TRUE	text	NO
D4	Cell	contains a blank value	text	NO
C6	Expression	=MOD(COLUMN(),2)=0	text	NO
C9:P9	Expression	=MOD(COLUMN(),2)=0	text	NO
D4,F4,H4,J4,L4,N4,P4	Cell Value	="YES"	text	NO
D4,F4,H4,J4,L4,N4,P4	Cell Value	="NO"	text	NO
D301:P301,C294:P300,C275:P291,C273:P273,C252:P258,C238:P250,C229:P236,C226:P227,C222:P223,C219:P220,C215:P216,C212:P213,C210:P210,C206:P208,C201:P204,C196:P199,C191:P194,C189:P189,C186:P187,C182:P184,C179:P180,C176:P177,C169:P174,C161:P167,C158:P159	Expression	=MOD(COLUMN(),2)=0	text	NO
C9:P399	Expression	=AND(D$4="YES",ISNA(C14))	text	NO
C6:P8	Expression	=AND(D$4="YES",ISNA(C10))	text	NO