# Conditional Formatting Formula

##### New Member
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.

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### etaf

##### Well-known Member
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

Last edited:

#### etaf

##### Well-known Member
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
101
113
125
137
149
1511
1613
1715
1817
1919
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A19Expression=AND(A2<-5,A2>=-10)textNO
A2:A19Expression=AND(A2<>"",A2>=-5,A2<=0)textNO
A2:A19Expression=A2<-10textNO
A2:A19Expression=A2>0textNO

#### Peter_SSs

##### MrExcel MVP, Moderator
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
AB
1a3
2b-16
3c0
4d-10
5e-20
6f-5
7g3
8h-15
9i-8
10j-3
CF
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1:A10Expression=B1<-10textNO
A1:A10Expression=B1<-5textNO
A1:A10Expression=B1<=0textNO
A1:A10Expression=B1>0textNO

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

#### Peter_SSs

##### MrExcel MVP, Moderator
You're welcome. Glad we could help. Thanks for the follow-up.

Replies
1
Views
155
Replies
9
Views
235
Replies
5
Views
113
Replies
2
Views
567
Replies
1
Views
618

1,191,118
Messages
5,984,749
Members
439,907
Latest member
Kayfabe

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back