Fill a box with a color with formula

Short answer: you don't. At least not easily.

Cells never push their value into other cells. Cells always pull their value from elsewhere.

So you cannot (easily) tell B1 to say "If I am 1, then go tell A1 to do something..."

What you can do is go the other way - you can tell A1 to say "IF B1 is 1, then tell me to do something".

A subtle, but important difference - A1 can pull B1's value, but B1 cannot force/push its value to A1.

The same is true for conditional formatting. B1 cannot set the color of A1. Only A1 can set its own color. Unfortunately, there are limited ways a cell can reference another cell for conditional highlighting.

The best way to think of this (or at least the way I think of it), is that conditional formatting is single-level deep only - each rule can check if one condition, and only one condition, is true. Saying "if B1 =1 then set my color to green" is two levels deep - you're first checking if B1 is 1, then you're using that result to make a second level decision, and that isn't going to work.

So the solution is to introduce a level of indirection that Numbers' conditional formatting can accept.

If you look at the Conditional Formatting rules, you can see that Numbers can check if this cell IS (or IS NOT) equal to certain values. It is possible to check if this cells value matches another cell's value (e.g. =B1, using one level of conditional), but not if another cell matches an unrelated condition (=B1=1, which is two levels).

So the trick is to create a new column of values. in this 'helper' column, say, Column C, you set the formula for C1 to:

=IF(B=1,A,"")

What this does is compare all the values in column B to see if they are 1. If so, it returns the value from the corresponding row in column A, otherwise it returns an empty string.

Now it's possible to set conditional formatting on Column A. For example, cell A1:

Here I'm checking if this cell (A1)'s text matches C1, and if so, sets the color to Green Fill.

It will match if B1 is '1', because C1 has a formula that copies column A to column C whenever B is 1.

Note that to set a cell reference in this way, you can't just type it - you need to click on the little green icon that appears in the value field, so that Numbers knows you want to use a reference rather than a value. Click the cell to compare, then click the check icon to confirm.

You should get something like this:

Column A is a list of names. Column B is a series of 1 or blank cells. Column C copies the value in Column A any time B=1, then the conditional formatting for Column A changes the background to green if the cell matches the value in column C.

Since you don't need the helper Column C to be visible, you can hide it.

Sure would be nice to be able to write a conditional in the Conditional Highlighting field directly, but for now this is the best way to go about it.

Hi lukasuhlback,

This discussion shows a different approach by Badunit:

https://discussions.apple.com/thread/255696143?answerId=260633038022&sortBy=rank#260633038022

Badunit uses "Text does not end with" as the Conditional Highlight rule. That allows us to fill several cells in row.

Happy Numbering!

Ian.