Conditional highlighting for a complete line
Hello
I searched the forum and elsewhere on the internet, no answer. However...
How can conditional highlighting be used for a complete line in Numbers?
null-OTHER, OS X El Capitan (10.11.6), Numbers
Apple Event: May 7th at 7 am PT
Hello
I searched the forum and elsewhere on the internet, no answer. However...
How can conditional highlighting be used for a complete line in Numbers?
null-OTHER, OS X El Capitan (10.11.6), Numbers
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.
Sorry for the delay. Man's flu. Thank you for your answers
@Yellowbox:
It works very well but it's only for one color. It's better than nothing and I can satisfy myself.
@ SGIII I know you like scripts a lot. What I see as a result is interesting. I had already watched one of your scripts here: https://discussions.apple.com/thread/6521790
The problem is that your script is in an English version and I'm afraid of screwing up something if I fiddle in there.
I will think a little more about all that as soon as I have my head in the right place.
MA
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:
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.
Thank you very much for all these efforts. I will carefully re-read your suggestions and see if I can do them as is or adapt them.
I am still very surprised that a function so useful and often requested on the internet is not yet included in Numbers.
SGIII
I reread your script and realized one thing, I already tried on scripts a long time ago. And then I remembered that these scripts were always in English.
It works.
I took a chance because what you write here: "and make sure Script Editor.app is listed and checked at System Preferences> Security & Privacy> Privacy> Accessibility."
I did not find that. Security & Privacy and Accessibility are two separate entities on my system. And I did not see Numbers.
I copied your table to be sure to do well. I notice that I do not achieve the same results as you in the "Total". Plus, if I write ($ 10.00), Numbers removes parentheses and writes: -10.00 $
My totals:
2000.00 $ 1112.75 $ 887.25 $
Yours:
$ 2200.00 $ 1202.75 $ 997.25
Some small questions:
1- Does the script accept text?
2- Why "testCol to 3" and not to 4?
3- What can be changed in if Statement? It is not simple this section. For example, I tried "is MORE than" instead of "is LESS than" and it did not work.
Someone who knows how to get by with these scripts can do a lot of things, I think.
Thank you!
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
It's pretty simple, I think. What I want is that if I make a schedule for 82 Canadiens games, I would like the lines where Montreal plays abroad to have a different color from the line where Montreal plays at home.
I found a parry that allows me, from the script to highlight at least one of the two lines. In a column I write the number 2 for home match and 4 for match abroad. I write testVal to 3 and bingo it works at least for that.
If the script allowed a second choice, say kind of testVal> 3 with another choice of color, that would be perfect.
p.s. Do not be embarrassed to tell me if my choice of words in English is not correct.
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
sethighlightColor1to {65535, 65535, 2713} --"yellow"
set highlightColor2 to {0, 0, 65416} -- "blue"
Where can we find the numbers for these color codes?
What is the difference between the codes in the first line (highlightColor1) and those in the second line (highlightColor2)? I understand this line better.
I already had an application that found the color codes that I proposed. But, it's not working on Mac for a few years now.
Conditional highlighting for a complete line