Odd Conditional Format Question / Result

Russ At Index

Well-known Member
Joined
Sep 23, 2002
Messages
678
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.
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,966
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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:

Russ At Index

Well-known Member
Joined
Sep 23, 2002
Messages
678
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Morning Mark ,
Thanks for the reply ,really appreciate it. As you can see cell K2 should be blue ... odd do you agree ??

1606987053221.png
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,966
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Are you sure that the cell is blank and you don't have a space in the cell?
 

Russ At Index

Well-known Member
Joined
Sep 23, 2002
Messages
678
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Hi,

No spaces , only a formula , but that should not effect the CF should it ?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,367
Office Version
  1. 365
Platform
  1. Windows
.. or a formula returning ""?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,367
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

Russ At Index

Well-known Member
Joined
Sep 23, 2002
Messages
678
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,367
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Glad it worked for you. :)
 

Russ At Index

Well-known Member
Joined
Sep 23, 2002
Messages
678
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,683
Members
415,920
Latest member
ExcelNoob28

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
Top