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?

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 A1:D7 = CF = Formula:
=COUNTIF($A1:$D1,0)1


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi colin,

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

sorry, have a typo in the formula above.

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


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

In article , Claus Busch
writes
Hi colin,

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

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 A1:D7 , 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.

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




In article , Colin Hayes
writes
In article , Claus Busch
writes
Hi colin,

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

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 A1:D7 , 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.

In article , Claus Busch
writes
Hi colin,

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

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

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

In article , Claus Busch
writes
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


Hi Claus

Excellent - thanks Claus. Works first time.



Best Wishes