Conditional formatting formula excluding any zeros

[graemeal]

In column J I have the following formula =IF(AND(C2<0.25,C2>-0.1),C2,0)

In conditional formatting (or by any other means) I would like to fill the J column cell "red" if values are between 0.25 and -.01, EXCLUDING any cell with zero in it.

I am stumped with how to exclude colouring the cell red if a zero is present.

Thank you

 

[AlanY]

your formula works

Book1
	A	B	C	D	E	F	G	H	I	J
1										0
2										0
3										0
4										0
5										0
6			0.15							0.15
7										0
8										0
9										0
10			0.2							0.2
11										0
12										0
Sheet1

Cell Formulas
Range	Formula
J1:J12	J1	=IF(AND(C1<0.25,C1>-0.1),C1,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J:J	Expression	=IF(AND(C1<0.25,C1>-0.1),C1,0)	text	NO

 

[graemeal]

your formula works

Book1
	A	B	C	D	E	F	G	H	I	J
1										0
2										0
3										0
4										0
5										0
6			0.15							0.15
7										0
8										0
9										0
10			0.2							0.2
11										0
12										0
Sheet1

Cell Formulas
Range	Formula
J1:J12	J1	=IF(AND(C1<0.25,C1>-0.1),C1,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J:J	Expression	=IF(AND(C1<0.25,C1>-0.1),C1,0)	text	NO

 

[graemeal]

Not if I am reading this correctly but I meant 0, not the word zero

 

[bobsan42]

One option is to use a formula for Conditional formatting:

=AND(J1<0.25,J1>-0.1,J1<>0)

Another way to do something similar is to use 3-color scale for CF.
Or you can use 2 separate conditions for formatting.

 

[jtakw]

Hi,

If I understand correctly, try CF, using formula:

Book3.xlsx
	C							J
1
2	0.25							0
3	-0.05							-0.05
4	0							0
5	0.24							0.24
6	0.26							0
7	0.01							0.01
8	0.26							0
Sheet724

Cell Formulas
Range	Formula
J2:J8	J2	=IF(AND(C2<0.25,C2>-0.1),C2,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J2:J8	Expression	=AND(J2<>0,J2<0.25,J2>-0.1)	text	NO

 

[graemeal]

meant not sure if I am reading it correctly

 

[AlanY]

posts #2 and #6 are basically the same, are they working for you?
if not, please give an example that doesn't

 

[graemeal]

Extremely grateful for all your input. Thank you so much.