# Odd Conditional Format Question / Result

#### Russ At Index

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

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
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
Morning Mark ,
Thanks for the reply ,really appreciate it. As you can see cell K2 should be blue ... odd do you agree ??

#### MARK858

##### MrExcel MVP
Are you sure that the cell is blank and you don't have a space in the cell?

#### Russ At Index

##### Well-known Member

Hi,

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

#### Peter_SSs

##### MrExcel MVP, Moderator
.. or a formula returning ""?

#### Peter_SSs

##### MrExcel MVP, Moderator

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
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
You're welcome. Glad it worked for you.

#### Russ At Index

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

Replies
10
Views
311
Replies
5
Views
71
Replies
20
Views
318
Replies
3
Views
654
Replies
4
Views
457

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.

### 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