Conditional highlight an entire row in Numbers

LourencoR wrote:

the column that says "Income" can also say "Expense", "Others" & "Mix" and I have 6 columns to be highlighted.

Try something like this. It needs one formula and only four "helper" columns if you have four distinct values in the Income/Expense/etc column.

In G2 filled right and down:

=IF($A2=G$1,"~","")

Use ; instead of ; in the formula if your region uses , as the decimal separator.

Then select the cells in the rows (not including the helper column) and add Conditional Highlighting rules like this:

Be sure to "anchor" the references within each rule by clicking the token and doing this:

SG

In Numbers it's not possible to conditionally highlight a cell solely based on the content of another cell, only on its own content.

The workaround I use is to create a duplicate table that I place under the main table. It can be as big as the main table (make a copy) or only cover a smaller range, it depends on your needs. Do not show the title.

In this example here I created a 3-cell table to highlight cells B5:D5 of Table 1. Here the formula for each is simply a copy of the cell containing the value used for creating the conditional highlight condition, but it could be as complicated as you want.

Then I create a text style that I call Invisible which sets the opacity of the text to 0% and apply it to all cells in the formatting table. Notice how the wheel icon has a green background indicating that the color palette applies to text.

Then I create a personalized conditional highlighting rule that I apply to the appropriate cells. Set text and background to the same color.

How it turns out.

Finally drag the formatting table over the correct location of the main table and send it to the back from the Format / Arrange pane. I suggest doing this only when your main table has its final appearance because cells in both tables have to match in size.

If you want to select a table that is behind another one, click on the V that appears on the sheet tab when you hover the mouse over.

Hi LourencoR,

To highlight a row depending upon a value, add a "helper" column for each "real" column. You can hide the helper columns when all is working.

Formula in the first helper column (D2): IF($A2="Income",A2,"")

Fill down and fill right.

Select all the "real" cells, then Format Panel > Cell > Conditional Highlighting.

Add a Rule, Text is...

Click on Done and the rule will be adjusted for all of the selected cells.

Hide the helper columns:

Done!

Regards,

Ian.

My goodness, this is genius! Thank you for the tip here, I was able to get exactly what I wanted with this. (I wanted entire DEBIT and CREDIT rows being red or green for easier visibility in an expense spreadsheet)

I had the same problem. My work around was to create a helper sheet that is a copy of the main sheet. When the cell I want to base highlighting on changes, the corresponding cells in the helper sheet copy all the values from the same row as that cell. I used IF. Then I created a conditional rule to highlight for each cell in the row if it is equal to the corresponding cell in the helper sheet.

So when the cell I want is "true", the entire row is copied to the helper sheet. That triggers the rule.

Here are the commands:

In the helper sheet:

IF(Main sheet::Table 1::$G2=TRUE,Main Sheet::Table 1::E2,"~")

In the Main sheet:

the conditional rule is: equal to helper::Table 1::$E2

I hope that helps.