Conditional Formatting assistance
 

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?

Conditional Formatting assistance
 

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

Conditional Formatting assistance
 

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

Conditional Formatting assistance
 

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.

Conditional Formatting assistance
 

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.

Conditional Formatting assistance
 

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

Conditional Formatting assistance
 

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

Conditional Formatting assistance
 

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