Odd Conditional Format Question / Result

Russ At Index

Well-known Member
Joined
Sep 23, 2002
Messages
706
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Morning ,
I have a weird problem , which TBH i do not understand .
Cell range J2 thru O2 ..... so six cells containing :
[11] [Blank Cell] ,[Blank Cell] , [Blank Cell] , [Blank Cell] , [154]
My conditioning is set as :
Cell Contains = Blank Value - Colour Blue
Cell Contains = 0 - Colour Blue
Cell Value = Between 1 & 30 - Colour Red
Cell Value = Between 31 & 100 - Colour Amber
Cell Value = Greater Than 100 - Colour Green

The result is [11 Red] Correct
The following 4 cells should be Blue , they are Green
The last cell [154] should be Green & Is Green.

Can someone please help me with the blank cells , they should be blue but they are green ..... am i missing something ??

Many thanks ,

Russ.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Try setting your blank condition as =""

Book1
JKLMNO
21150154
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J2:O2Cell Value=""textNO
J2:O2Cell Value=0textNO
J2:O2Cell Valuebetween 1 and 30textNO
J2:O2Cell Valuebetween 31 and 100textNO
J2:O2Cell Value>100textNO


Can you try and post a sample using the boards XL2BB addin so we can see exactly what conditions are set if the above doesn't work for you please.
 
Last edited:
Upvote 0
Morning Mark ,
Thanks for the reply ,really appreciate it. As you can see cell K2 should be blue ... odd do you agree ??

1606987053221.png
 
Upvote 0
Are you sure that the cell is blank and you don't have a space in the cell?
 
Upvote 0
Hi,

No spaces , only a formula , but that should not effect the CF should it ?
 
Upvote 0
Try
- changing the blue rule to "Use a formula to determine which cells to format and
- use the formula =LEN(K2)=0 and
- move that rule to the top of the list
 
Upvote 0
Hi Peter ,
We have a ;)winner ,

Thank you Peter & Mark for helping me out today , it makes my presentation tomorrow a lot easier .

Have a great weekend , stay safe ,

Russ.
 
Upvote 0
You're welcome. Glad it worked for you. :)
 
Upvote 0
Hi Peter , Mark ...

Slightly bizarre request , I've searched but to no avail ....

Below is my data , is there a nice and easy way to count all red cells , amber cells etc etc within a range ?

Again any pointers greatly accepted ....

Russ


1607003624972.png
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

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