Conditional Formatting Formula

[beastylad123]

Hi,
I am trying to change the colour of a cell based on the value in a 2nd cell.

Currently I want the criteria to be:

>0 ( Anything over 0) Colour will change to blue
>-10 ( Anything less than minus 10) Colour will change to red
AND(A1>=0,A1<=-5) (Between 0 and minus 5) Colour will change to green
AND(A1>=-5.01,A1<=-10) (Between minus 5.01 and minus 10) colour will change to Orange

Currently everything just goes green or red.

Any ideas on whats wrong with the conditional formatting formula I am using?

TIA.

 

[etaf]

You may need 4 conditional rules , BUT you need to put them in order and STOP IF TRUE
Although they values do not seem to overlap , which can cause an issue

the formulas would be

=Cell with value , may need a $ if selecting a range >0

=Cell with value , may need a $ if selecting a range <-10. not >-10

the value
AND(A1>=0,A1<=-5)
cannot be greater than 0 and less then -5 ?
do you want between -5 and 0
AND(A1>=-5,A1<=0)

same for here
AND(A1>=-5.01,A1<=-10) change to
you want between -5.01 and -10

AND(A1<=-5.01,A1>=-10)

Or i may have miss understood that

 

[etaf]

also i would not use the 5.01 - just use < -5
and NOT <=
that way you cover any possible fraction

blank cells will be seen as zero
so you may want to use NOT blank
AND(A1<>"", A1>=0,A1<=-5)

Book6
	A
2	-15
3	-13
4	-11
5	-9
6	-7
7	-5
8	-3
9	-1
10	1
11	3
12	5
13	7
14	9
15	11
16	13
17	15
18	17
19	19
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A19	Expression	=AND(A2<-5,A2>=-10)	text	NO
A2:A19	Expression	=AND(A2<>"",A2>=-5,A2<=0)	text	NO
A2:A19	Expression	=A2<-10	text	NO
A2:A19	Expression	=A2>0	text	NO

 

[Peter_SSs]

Welcome to the MrExcel board!

Any ideas on whats wrong with the conditional formatting formula I am using?

You have not given us the Conditional Formatting formula that you are using.


Is this the sort of thing you are after?

21 11 30.xlsm
	A	B
1	a	3
2	b	-16
3	c	0
4	d	-10
5	e	-20
6	f	-5
7	g	3
8	h	-15
9	i	-8
10	j	-3
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A10	Expression	=B1<-10	text	NO
A1:A10	Expression	=B1<-5	text	NO
A1:A10	Expression	=B1<=0	text	NO
A1:A10	Expression	=B1>0	text	NO

 

[beastylad123]

Hey thanks both!! These are working perfectly, tired me not looking at it correctly.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.