Conditional Highlighting Entire Row (Based on a single cell containing ":")

ZanderCam7 wrote:

Since the only time I would use a colon (":") is in the rows I want to highlight black and switch the text to white, I tried to set up a Conditional Highlighting rule:

Yes, you can do this with Conditional Highlighting with a Rule like this:

Add an additional column with a formula that places a character if there is a : in Column B. You can hide that column later if you want.

In H2 of my example, filled down, I have:

=IF(COUNTMATCHES($B2,":")=0,"~","")

The I selected the range to be highlighted, A2:G10 in my example, and set up the rule.

Note that you need $H2 in the address token (i.e. Preserve Column, but don't Preserve Row):

I think I got this general 'Text ends with' ("blank") approach some years ago from a post by Badunit. Kudos to him!

SG

You didn't miss anything. Numbers does not include a direct way to highlight an entire row. Each cell has to highlight itself individually. Selecting A2:G10 lets you create individual rules for all those cells at once, all of which will compare themselves to the corresponding cell in column H if the reference is $H2

The "text ends with" method is a trick to highlight a row using the knowledge that all text ends with the null string "", or at least it does according to the highlighting rules. The trick requires an additional column (cell) that will be either "" or an obscure character that no cell's text ends with. Multiple rules (such as for multiple cell colors) can be created, each rule requiring an additional column.

Jerrold Green1 wrote:

I never would have guessed that "Text Ends With" would look at the content of the last cell in a row, not necessarily in the Selection, and ignore what was in the other cells included in the Selection

'Text Ends With' looks at the top left cell of the exception (not the content of the last cell in a row) and compares its value to the value in the cell specified in the Rule, in my example H2. This just happens to be the last cell in the row, but it doesn't have to be.

The anchors in the reference help tell Numbers what the other cells in the selection should be compared to.

SG

Jerry,

Perhaps documented somewhere but, if so, I haven't seen it. The user interface of the "new" Numbers is deceptively simple. Under the surface are features and behaviors that are easy to miss. Just have to keep poking around to see what works.

SG

I never would have guessed that "Text Ends With" would look at the content of the last cell in a row, not necessarily in the Selection, and ignore what was in the other cells included in the Selection, and act on the entire Row. Also the relative addressing with the Column reference being pinned intrigued me.

I've spent decades thinking that Apple missed the boat on that one, only to learn it was just hidden.

Jerry

Badunit,

Also important in some situations, there seems to be no limit to the number of rules with your method since it uses the variable comparison feature. With "Add a Rule" I think 10 rules is the limit.

Jerry

Z,

Are the Body Cells in Columns C-Q computed or directly entered?

Jerry

SG,

Is that super-useful behavior documented anywhere, or is it just some kind of Easter Egg?

Jerry