Conditional highlighting for a complete line

Hi Marc,

Data are in Table 1.

Create another table (Table 1-1) that Conditional Highlighting can use to compare. Table 1-1 can be hidden on another Sheet when you get this to work.

I used Check boxes in Table 1 as a trigger.

Formula in B2 of Table 1-1 (Fill Right and Fill Down)

=IF(Table 1::$A2,Table 1::B2,"")

Conditional Highlighting in Table 1 for the selected cells

To get cell references into the Highlighting rule, click on this

Then click on the top left cell (B2 in this example).

The rule will automatically fill and adjust to all selected cells.

Regards,

Ian.

A script can be efficient for highlighting an entire row. No need to know scripting to use it, which takes a few seconds to set up.

For example the script below will produce this effect:

To use just copy-paste into Script Editor (in Applications > Utilities folder) and make sure Script Editor.app is listed and checked at System Preferences > Security & Privacy > Privacy > Accessibility.

Then change the first three lines and the if statement to match what you want to highlight, click in the table, and click the 'run' button in Script Editor. To undo click the table and hit control-z a few times.

SG

set testVal to 50

set testCol to 3 -- column "C"

set highlightColor to "yellow"

tell application "Numbers"

tell front document's active sheet

tell (first table whose selection range's class is range)

repeat with r in rows

if r's cell testCol's value is less than testVal then ¬

set r'sbackground color to highlightColor

end repeat

end tell

end tell

end tell

You can try something like the script below. It assumes you have one Header Row and that the second column in your screenshot is column "B" (i.e., there are no columns to the left). You can adjust the number in the second line of the script to fit your table. Post if you run into problems.

SG

set testVal to "Montréal"

set testCol to 2 -- column "B"

set highlightColor1 to "yellow"

set highlightColor2 to "blue"

tell application "Numbers"

tell front document's active sheet

tell (first table whose selection range's class is range)

repeat with r in rows 2 thru -1 -- skip header row

if r's cell testCol's value contains testVal then

set r'sbackground color to highlightColor1

else

set r'sbackground color to highlightColor2

set r'scells'stext color to highlightColor1

end if

end repeat

end tell

end tell

end tell

In Numbers, you can use the color picker, like if you are going to choose a color to fill a cell. Or, your Mac may have the Digital Color Meter app in Applications/Utilities that will let you mouse around on the screen to determine color values of various things. Multiply the results by 257 to get the 16-bit values.

Choose one of the standard colors from the color well then click on the color wheel next to it and the values for that color will show up in the Colors window. Multiply each by 257.

Or, with the Color window open, use the eyedropper to find codes for colors on the screen or use the sliders to make your own colors.

Hmm. I just tried the way I remember we used to do it and it no longer works like it did. If I recall, we would do this:

1. Create a new table
2. In each cell put a formula like =Table 1::B to transfer the data from Table 1 to the new table so a conditional highlight could be applied
3. Create the conditional format rules
4. Set the text opacity to zero so all you see is the highlighted color, not the data.
5. Get rid of the table gridlines and stuff so all that is shown is the highlighting
6. Slide the table under the other and resize accordingly.

I am having an issue with step 4. I cannot set the opacity of the text to zero. If, for instance, my highlighting rule ends says it should be a red background, the text is black, no matter what color or opacity I choose for it. Without the ability to hide the text, this method doesn't work.

It can be difficult to find things in the forum, even when you know exactly what search terms to use. The question has been asked a number of times over the years but, unfortunately, the answer is not a good one. There is no good built-in way to do it. One workaround has been to create a separate one-column table with one really wide column that has the conditional formatting in it then slide it underneath of your real table.

I was remembering wrong I guess. Been a while. Step 3 requires custom formatting such as red text on red background to "hide" the text. No step 4.

When resizing the table, it might be easiest to use the Arrange tab in the sidebar to fine tune the size.

My table is from the "Personal Budget" template at File > New in your menu.

The script doesn't change any values in a table. In my example it simply looks for values in column C (=column 3 because that is what testCol is set to) that are less than 50 (the TestVal) and highlights those rows.

Perhaps if you give a screenshot of your table and explain what you want to highlight (the "rules" you want) then I can post a modified script.

SG

Can we replace the name of a color by its code?

Yes, you can use rgb codes in AppleScript. You could, for example, change the script to the one below (and substitute the codes for other codes that you like better than the plain "yellow" and "blue.")

SG

set testVal to "Montréal"

set testCol to 2 -- column "B"

set highlightColor1 to {65535, 65535, 2713} --"yellow"

set highlightColor2 to {0, 0, 65416} -- "blue"

tell application "Numbers"

tell front document's active sheet

tell (first table whose selection range's class is range)

repeat with r in rows 2 thru -1 -- skip header row

if r's cell testCol's value contains testVal then

set r'sbackground color to highlightColor1

else

set r'sbackground color to highlightColor2

set r'scells'stext color to highlightColor1

end if

end repeat

end tell

end tell

end tell