conditional formatting criteria

  • Thread starter Thread starter Marco
  • Start date Start date
M

Marco

I desire color a cell if in a range exit 0 value.
My cells are in C23:C120 and the 0 value are in AF23:AY120
so
C23 color for ex red if in AF23:AY23 exit one or more cells with value 0
C24 color red if in AF24:AY24 exit one or more cells with value 0
....
C120color red if in AF120:AY120 exit one or more cells with value 0

Is it possible ?

or better
C23 color for ex red if in AF23:AY23 exit one or 2 cells with value 0
C24 color for ex yellow if in AF24:AY24 exit more than 2 cells with value 0
....

thanks
 
Marco,

Select C23. Format - Conditional Format. Change "Cell value is" to "Formula is"

=COUNTIF(AF23:AY23,0)
or more correctly:
=COUNTIF(AF23:AY23,0)>0

Set your formatting, and OK out of it.
 
But if you want the conditional formatting only if there are 1 or 2 cells with 0, but not
more, then use:

=AND(COUNTIF(AF23:AY23,0)>0,COUNTIF(AF23:AY23,0)<3)

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
 
Hi,

For the Better Yet part of your request:
Select the range C23:C120 and choose Format, Conditional Formatting, pick
Formula is from the first drop down and enter the formula
=COUNTIF(AF23:AY23,0)>2
Click the Format button, choose the Patterns tab and pick YELLOW, click OK
once.
Click the Add button, choose Fromula is and enter the following
=COUNTIF(AF23:AY23,0)
set the color for this conditon to RED.
 
Back
Top