# Formula in Conditional formatting (v4.0.0.3) for large areas [closed]

We used to work a lot with conditional formatting. After updating to 4.0.0.3 the following was not working anymore:

Rows from 2 to 39 are completed with data collected from real life. In row 40 we have a model on each column. data is collected for multiple criterias (Ak2:CA39). We check what we find on the field with the model and mark with style RED those entries that are not matching the model.

For this we setup the Formula =if(isempty($a2),0,if(ak2=ak$40,0,1)) in the Conditional Formatting of AK2 for example. Afterwords we copy the conditional formatting to the rest of the cells, Ak2:CA39.

But now it doesn't work at all. Not even for one cell!!!

Any ideas? How can you do it?

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2015-10-21 15:43:08.108710

Sort by » oldest newest most voted

Try selecting first the range where you want to use the formula for the conditional format, and then go to the conditional format.

Take a look at the open bugs about copy cells with conditional formatting

more

It seams that if I use the conditional formatting for just one cell, the formula works =if(ISBLANK($a2),0,if(ak2=ak$40,0,1))

The only problem was when it was pasted special to other cells. It is at this moment a bug and it is worked upon.

However, there is a work around:

1. Make the first cell with the right conditional formatting formula
2. FILL DOWN/RIGHT the cell (NOT copy/paste special)
3. Enter the data into the cells
4. and it works...

I hope the bug is going to be resolved. As i saw in the description it behaves much worse then this.

more