# conditional formatting formula if two cells equal 0

#### jontyoc

##### Board Regular
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?

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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)

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.

Replies
5
Views
140
Replies
4
Views
365
Replies
5
Views
112
Replies
3
Views
171
Replies
18
Views
341

### Forum statistics

1,203,491
Messages
6,055,727
Members
444,814
Latest member
AutomateDifficulty

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back