conditional formatting formula if two cells equal 0

[jontyoc]

Hi All

Hoping you can help with the formatting I need for this.

I have the following extract from my data sheet:

	B	C	D	E
2	62	75.0%	79.0%	100.0%
3	0	0.0%	0.0%	0.0%
4	20	75.0%	60.0%	0.0%
5	3	75.0%	100.0%	100.0%
6	57	75.0%	84.2%	100.0%
7	24	75.0%	62.5%	0.0%
8
9	0	0.0%	0.0%	0.0%
10	56	75.0%	78.6%	100.0%
11	62	75.0%	64.5%	0.0%
12	0	0.0%	0.0%	0.0%
13	21	75.0%	90.5%	100.0%

<TBODY>
</TBODY>

I have the formulas for conditional formating of column E set as such:

if blank cell no format and stop.
if cell above a value then format fill green.
if cell below a value then fill red.
if cell between values then fill amber.

And that works fine to a point.
However, if the cell in B is a 0 value then I am not interested in any formatting regardless of the value in E
formatting by formula and have tried the following.

=If(E=B=0,,E) of if(E2=B2=o,,E2)
Then copy it down the column.
But it doesnt seem to work for even one cell never mind the column.

or just had a thought and still not sure how to do it.
Conditional formatting based on only a zero figure in B?

Thanks in advance for your time.

 

[Special-K99]

You cant say

=If(E=B=0,,E) or if(E2=B2=o,,E2)

it has to be

=IF(AND(E=B,E=0),,E)
or
=IF(AND(E2=B2,B2=0),,E2)

 

[jontyoc]

that doesnt work with the conditional formatting though.
I have managed it one one cell (E9) with this formula:

b9=0 then no format and stop.

But I want to then use this down the whole column and across to column J and O.
Is there anyway of selecting the three columns as one (E, J & O) and amending that formula so that if the cell value on that row is 0 no formatting is done.

I cant copy and drag the cell down as different cells have different formulas than the initial cell, and I would need to repeat several dozen times on each sheet. 6 sheets in total.