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

#### MARK858

##### MrExcel MVP
Try using Countif and Countifs to count the condition.

Cell Formulas
RangeFormula
A1:L25A1=RANDBETWEEN(0,300)
P2P2=COUNTIF(\$A\$1:\$L\$25,0)
P3P3=COUNTIF(\$A\$1:\$L\$25,"")
P4P4=COUNTIFS(\$A\$1:\$L\$25,">0", \$A\$1:\$L\$25,"<31")
P5P5=COUNTIFS(\$A\$1:\$L\$25,">31", \$A\$1:\$L\$25,"<101")
P6P6=COUNTIF(A1:L25,">100")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1:L25,N2:N6Cell Value=0textNO
A1:L25,N2:N6Cell Valuebetween 1 and 30textNO
A1:L25,N2:N6Cell Valuebetween 31 and 100textNO
A1:L25,N2:N6Cell Value>100textNO

Btw it would be much more helpful if you used the boards XL2BB addin rather than post images so we don't have to retype/makeup data.

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Replies
10
Views
314
Replies
5
Views
71
Replies
20
Views
318
Replies
3
Views
654
Replies
4
Views
459

1,127,105
Messages
5,622,762
Members
415,926
Latest member
jerrynababa

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