3

So I have this spreadsheed with two areas (D7:AD16 and D18:AD27). Now I want to format the colors in D18:AD27 such that yellow color is added to every cell that is empty in D18:AD27 but not in D7:AD16.

The top and bottom area I want to compare and format cell by cell. Ignore the green cells.

I use the following custom formula that is applied to one row like this:

=AND(ISBLANK(D23:AD23);NOT(ISBLANK(D12:AD12)))

the conditional format tab:

enter image description here

And it produces the desired result for that row:

enter image description here

Problem

when I try to do this for 2 dimensions I change the formula to

=AND(ISBLANK(D18:AD27);NOT(ISBLANK(D7:AD16)))

and apply it to the area like this:

enter image description here

Which I thought would work, however, this removes all the yellow color-formatting from the area so how can I achieve the desired result without doing this for every row manually?

Improve this question

1 Answer 1

Reset to default
2

Of course I just happen to solve it when I was re-creating the problem for this question. Apparently the formula should be for one row and then it is applied row by row when applying it to an area.

It worked by using this custom formula for only the top row:

=AND(ISBLANK(D18:AD18);NOT(ISBLANK(D7:AD7)))

and then applying that formula to the whole area like this:

enter image description here

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.