conditional formatting criteria

[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

 

[Earl Kiosterud]

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.

 

[Earl Kiosterud]

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

 

[ShaneDevenshire]

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.