Conditional Formatting

[Badnames]

I am trying to make a range of cells highlight if the number in their cell is a lower % than cell C15. The other condition is that cell n21 or u 21 have to have a value greater than 0. I'm not sure how I can write this in the conditional formatting rules.

Can anyone give me an example of what i should enter?

Thanks

 

[Badnames]

So I figured out how to apply it to one cell, if I put an if statement. Like in A21, if(and(a21<c15,or(c21>0,n21>0)).

My problem is I need to remove the A21 so that the format looks at the current cell, not always refers to a21, but maybe a30 or a40.

 

[T. Valko]

So I figured out how to apply it to one cell, if I put an if statement. Like in A21, if(and(a21<C15,OR(C21>0,n21>0)).

My problem is I need to remove the A21 so that the format looks at the current cell, not always refers to a21, but maybe a30 or a40.

What version of Exel are you using?

What is the EXACT range of cells that you want to format?

 

[p45cal]

Excel Workbook
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
6			15%	76%	98%	91%	18%
7			62%	20%	36%	11%	49%
8			27%	57%	99%	43%	45%
9			50%	9%	65%	3%	85%
10			27%	52%	0%	44%	24%
11			70%	96%	14%	74%	13%
12			21%	44%	79%	94%	15%
13			37%	51%	49%	25%	88%
14			22%	93%	71%	63%	90%
15	25%		55%	77%	62%	38%	96%
16			22%	30%	45%	73%	14%
17
18
19
20
21												0							4
Sheet9

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E6	1. / Formula is =AND(E6<$C$15,OR($N$21>0,$U$21>0))	Abc

Select the whole range E6:I16, top left to bottom right, so that E6 is the active cell, then in the Use Formula section of Conditional Formatting enter the formula shown, choose your format and OK. You can see in the formula a single reference to E6, if your range is different then select as instructed, but substitute the top left cell address (make sure there are no $ symbols for that one cell reference) for E6.

It's not clear whether you mean N21 AND U21 need to be greater than 0 or just one of them, so this may not be right as I've guessed that you meant either one or the other had to be greater than 0.

 

[Badnames]

I'm using excel 2007

The exact range of cells is AN21:AN120. If N21 or U21 is greater than 0 than I need the format.

an example:

if an21 is less than c15 and n21 is greater than 0 format an21

if an31 is less than c15 and u31 is greater than 0 format an31

Also N21:n120 and u21:u120 will be the reference to the an21:120, like n40 and u40 will be referencing an40

 

[Badnames]

The formula I have now looks something like this:

=AND(AN21<$C$15,OR($N21>0,$U21>0))

The format still is not changing. Could it be because cell c15 and an21:120 are %?

 

[p45cal]

it seems fine here:

Excel Workbook
	C	K	L	M	N	O	P	Q	R	S	T	U	V	AN
15	50%
16
17
18
19
20
21					4							4		60%
22					4							4		16%
23					0							4		42%
24					0							4		95%
25					0							4		77%
26					0							4		60%
27					0							4		39%
28					4							0		90%
29					4							0		54%
30					4							0		28%
31					4							0		41%
32					4							0		84%
33					0							0		69%
34					0							0		95%
35					0							0		36%
36					0							0		95%
37					0							0		46%
38					0							0		18%
39					0							0		74%
40					0							0		27%
41					0							0		50%
42					4							4		77%
43					4							4		2%
44					4							4		85%
45					4							4		3%
46					4							4		82%
47					4							4		96%
Sheet

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AN21	1. / Formula is =AND(AN21<$C$15,OR($N21>0,$U21>0))	Abc

 

[Badnames]

Thanks for your help p45cal.

Don't know why, I restarted my comp and reentered the formula and it works. Weird.