Conditional Formatting Formula

beastylad123

New Member
Joined
Nov 30, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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:
Upvote 0
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
 
Upvote 0
Solution
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
 
Upvote 0
Hey thanks both!! These are working perfectly, tired me not looking at it correctly.
 
Upvote 0
You're welcome. Glad we could help. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top