conditional highlight an entire row?

HI fiasko,

Conditional highlighting rules apply to individual cells, and require comparing the content of the individual cell to either a fixed value or the value in another cell.

To highlight whole rows using conditional highlighting requires using one of two techniques:

1. Set up a second single column table with the same numbers of rows as the first, the same row height, and a column width equal to the width of the first table. (The easiest way to do this is to add a column to the existing table, select that column and drag it away from the table to form a new single column table. Place that table below the first, aligning the left edges, then drag the right edge of the column reference tab to expand the column width to align with the right edge of the first table.)
   Enter a formula in each row that will return TRUE when the 'single cell' in that row of the first table meets the condition for highlighting.
   Set the default text colour on the 'highlight' table to white. Set the conditional highlighting rule to colour both the fill and the text to the same highlight colour when the Text is TRUE.
   OR
   
2. Copy the original table, then paste it to an empty place in the canvas or on a new sheet)
   Delete the content of any cells in the copy that are paired with cells that will not be highlighted on the original.
   In each row corresponding to a row to be highlighted in the original table, enter a formula fitting this form and description: (The cell coordinates B2 assume this is entered in B2 of the copy and the 'single cell' is in column D of each row of the 'original' table
   IF(original::$D2 meets condition, original::B2,"")
   The conditional highlighting rule for all cells to be highlighted in the original table is Text is original::B2

Note that this rule fails with an empty cell due to the copy containing a zero. Probably preventable with some formula tweaking.

The "copy" table would be hidden in this method.

Both of these methods are 'live' and provide 'instant updates.

A disadvantage is the need to edit two tables when there is a change in the number of rows in the original.

Regards,

Barry

HI MW,

I'm sure there is an AppleScript solution that can accomplish this without the use of a second table. The disadvantage with using a script or an Automator service is that the user has to manually trigger the script to update the highlighting.

A quick search on 'highlight row' brought up several hits, including this question Re: Conditional Highlighting for entire rows from late 2014.

There are two scripts in the thread. SGIII's is the more direct, the other looks like an interesting experiment, but may be less practical.

Here's my test table "original" with the conditional highlighting rule removed.

SGIII's script (edited to match the column and 'testValue' used in my table) was run before adding the adding the "yes" to D7.

After adding "yes" and running the script again:

Regards,

Barry

OOPS!

Just noticed a shortcoming of that particular script—it only works in one direction, applying the fill colour to a row containing a match to the test value.

Unfortunately, it doesn't remove that fill if run later when the test value is no longer in that cell, and can be seen here, where the script was run again after deleting "yes" from D3 and D7, and added to D9.

Here's an amended version of SG's script from the linked thread.

The revisions solve the 'unhighlighting' issue but leave another related issue discussed below:

As can be seen, the rows no longer containing yes are no longer highlighted...BUT

The original colour fill on the header row and header column has also been replaced, either by the hilight colour or by the 'lolight colour (white), masking the fact that these are headers,

and the unhilighted cells now have a colour fill (white) applied where before they had "No fill".

Here is the edited script. SG may have additional changes.

--highlight a row based on value found in column testColumn on that row

--Script by SGIII

--additions by Barry

property testColumn : 4 -- 1 is column A, 2 is column B, etc (changed to match example)

property testValue : "yes" -- changed to match example

property highlightColor : "red"

property lowlightColor : "white" -- added property. colour for unhighlighted rows B

tell application "Numbers" to tell front document to tell active sheet to tell (first table whose class of selection range is range)

repeat with r in rows

set v to r'scelltestColumn'svalue

if v is testValue then set r'sbackground color to highlightColor

if v is not testValue then set r's background color to lowlightColor -- added line B

end repeat

end tell

Regards,

Barry