Conditional Formatting with IF(AND)

[bubblystace]

Hi All,

I'm stuck on some CF and hope someone can help please

I want to CF to highlight non-blank cells if B4 is not N/A. I've tried using formula below but not sure why it isn't working.
=IF(AND($D4='ConditionRatings'!$A$1:$A$4),AND(COUNTBLANK($D1:$D5)>0))

E.g. If B4 is "1" then B1, B2, B3 and B5 need to be populated, if not they are to be shaded red.

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD][/TD]
[TD]COLUMN A[/TD]
[TD]COLUMN B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Measurement[/TD]
[TD]10LM[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Material[/TD]
[TD]Brick[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Height[/TD]
[TD](red fill)[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Condition Rating[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Remaining Life[/TD]
[TD](red fill)[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

The formula you posted won't work, it's syntax and logic is incorrect in more ways than 1.
Without knowing more about your setup, and just based on your description, you can use the following formula as your CF formula in B1, B2, B3, B5.

Book1
	A	B	C	D
1	Measurement	10LM		FALSE
2	Material	Brick		FALSE
3	Height			TRUE
4	Condition Rating	1
5	Remaining Life			TRUE
Sheet8

Cell Formulas
Range	Formula
D1	=AND(B$4<>"NA",B1="")

Enter formula as CF formula for B1, format Fill Red, then use Format Painter to copy for B2, B3, B5.