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.
 

MARK858

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

Some videos you may like

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

Watch MrExcel Video

Forum statistics

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