Conditional Formatting assistance

[Colin Hayes]

Hi All

I'm trying to solve an issue with conditional formatting.

Essentially , if any of the cells in the selected area contain 0.00 ,
colour every selected cell red.

eg:

1 16.00 2.00 3.5 1.20
2 18.00 2.00 2.5 1.28
3 20.00 2.00 1.5 1.33
4 22.00 0.00 0.0 1.33
5 22.00 0.00 0.0 1.33
6 22.00 0.00 0.0 1.33
7 22.00 0.00 0.0 1.33

In this example all the cells in rows 4 to 7 would be red.

Can someone advise?

 

[Claus Busch]

Hi Colin,

Am Tue, 29 May 2012 21:20:57 +0100 schrieb Colin Hayes:

1 16.00 2.00 3.5 1.20
2 18.00 2.00 2.5 1.28
3 20.00 2.00 1.5 1.33
4 22.00 0.00 0.0 1.33
5 22.00 0.00 0.0 1.33
6 22.00 0.00 0.0 1.33
7 22.00 0.00 0.0 1.33

select A17 => CF => Formula:
=COUNTIF($A1:$D1,0)>1


Regards
Claus Busch

 

[Claus Busch]

Hi colin,

select A17 => CF => Formula:
=COUNTIF($A1:$D1,0)>1

sorry, have a typo in the formula above.

=COUNTIF($A1:$D1,0)>0


Regards
Claus Busch

 

[Colin Hayes]

Hi colin,


sorry, have a typo in the formula above.

=COUNTIF($A1:$D1,0)>0


Regards
Claus Busch

Hi Claus

Thanks for getting back with your solution.

I applied the formula

=COUNTIF($A1:$D1,0)>1

via CF to A17 , but it makes all the cells in *all* the rows go red ,
and not just those with 0.00 in the row.

I tried changing some of the values after the close bracket , but all
seem to leave the cells unaffected.

 

[Colin Hayes]

Hi Claus

OK please ignore my last message. I made a mistake on entry of the
formula. I have corrected my error and now it works fine.

Thank you for your help.

Best Wishes

 

[Colin Hayes]

Hi colin,


sorry, have a typo in the formula above.

=COUNTIF($A1:$D1,0)>0


Regards
Claus Busch

Hi Claus

You kindly helped my query with this formula for conditional formatting
:

=COUNTIF($A1:$D1,0)>0

This applies the formatting for any row that includes a 0.00 value.

Is it possible do you know to have it apply only for rows which have two
0.00 values?

Grateful for your advice.



Best Wishes

 

[Claus Busch]

Hi Colin,

Am Thu, 31 May 2012 18:02:17 +0100 schrieb Colin Hayes:

Is it possible do you know to have it apply only for rows which have two
0.00 values?

if you want to apply it for rows with exact two 0.00 values:
=COUNTIF($A1:$D1,0)=2
But if you want to apply it for rows with two or more 0.00 values:
=COUNTIF($A1:$D1,0)>=2


Regards
Claus Busch

 

[Colin Hayes]

Hi Colin,

Am Thu, 31 May 2012 18:02:17 +0100 schrieb Colin Hayes:


if you want to apply it for rows with exact two 0.00 values:
=COUNTIF($A1:$D1,0)=2
But if you want to apply it for rows with two or more 0.00 values:
=COUNTIF($A1:$D1,0)>=2


Regards
Claus Busch

Hi Claus

Excellent - thanks Claus. Works first time.



Best Wishes