I use conditional highlighting to set the fill color of one cell but would like the fill color applied to the entire row, does Numbers permit this?
iMac (Retina 5K, 27-inch, Late 2014), OS X Yosemite (10.10.4), Numbers v 3.5.3 (2150)
Hi Nev Qld,
To do this withing Numbers requires a workaround. You can create a second table behind the first that shows the highlighting you want. If you are interested I can work up an example.
If the highlighting is something that does not need to change frequently, SG's script here Can I make Pop Up Menu Cell adjust entire ROW color? could serve you well.
quinn
Here is a similar solution. It uses a shader table which is placed behind the original table:
Table 1 is the original table. Table 2 is the shader table. In this case I entered a formula in cell A1 of Table 2 like this:
A1=Table 1::B1
this is shorthand for... select cell A1 of Table 2, then type:
=Table 1::B1
now select cell A1, and set up the conditional formatting like shown in image above
once you have the formula and conditional formatting set up, select cell A1, copy
how select all cells in the column, paste
You should notice:
1) that the source data for shading is Table 1 column B (you can change this as needed)
2) that the shader table only has one column AND the column is stretched to be the same width as all the columns of the original table. This makes it easier to shade each row.
now select Table 2 and then select the menu item "Arrange > Send To Back"
now drag Table 2 so it aligns with Table 1. Now the rows will appear to shade as you change data in Table 1
If you need to color an entire row in Numbers then AppleScript is the way to go. Minimal setup (basically copy-paste). No fussing about with formulas and extra tables. And updating the highlighting is one-click away, suitable even if you constantly change your values and need to update frequently. If you post a screenshot of your table structure it will be easy to customize the script posted in the linked thread.
SG
Although new to Mac & Numbers I'd like to go with AppleScript (have some experience with Excel/macros):
Numbers V 3.5.3 (2150)
The Numbers spreadsheet has two sheets (simplified for this example).
Column D in Sheet1 is used to find a match in Sheet2 Column A.
If a match is found, then the number in Sheet1:C in indexed into 1st-4th Columns on Sheet2, resulting in the corresponding position.
Formula in Sheet1 Column D is:
Sheet1 Cell D2: IFERROR(IF(A2=Sheet2::Table 1::A$2,MATCH(C2,Sheet2::Table 1::H$2:K$2,0)),"")
Sheet1 Cell D3: IFERROR(IF(A3=Sheet2::Table 1::A$2,MATCH(C3,Sheet2::Table 1::H$2:K$2,0)),"")
Conditional highlighting is then applied to Col D as shown in Sheet1.
Sheet1 is currently about 8000 rows and is externally generated (by Python procedure):
Sheet2 (also externally generated by python):
I have found Script Editor V2.7(176) AppleScript 2.4 but not sure how this is used to drive Numbers, there doesn't appear to be a Numbers menu to record steps?
If I understand correctly you want to color the entire row yellow if the value column in D is 1, gray if 2, orange if 3, blue if 4.
If that is is correct, then the script below will do that.
The results will be something like this:
As you will note, unlike VBA in Excel, AppleScript is "external" to Numbers. You can't attach the script to a button within Numbers but you can easily make it a menu pick by clicking 'Show Script menu in menu bar' in Script Editor's Preferences > General:
Then you can click on the icon that looks like a "scroll" at the top of your screen to open the Scripts Folder and move the script into there, where it is easily accessed when you need to run it. If needed you can also place the script into an Automator Service that will appear in your Numbers > Services menu and attach a keyboard shortcut via System Preferences > Keyboard > Shortcuts > Services. If need more details on that, just ask.
If for some reason your script just "isn't doing anything" check to be sure Script Editor is checked here:
And after clicking give it some time. AppleScript, though convenient, is no speed demon, and 8000 is a lot of rows.
If you run into problems just post.
SG
set testCol to 4 -- column D
set theColors to {"yellow", "gray", "orange", "blue"}
-- order here is significant: yellow if value is 1, gray if 2, etc. -- and have a color for every possible value in D
tell application "Numbers"
tell document 1 to tell active sheet
set t to first table whose class of selection range is range
repeat with i from 2 to count t's rows -- from 2 to skip Header Row
tell t's row i
set v to its cell testCol's formatted value
if v is not missing value then
set its background color to theColors's item v
end if
end tell
end repeat
end tell
end tell
Thanks SGIII, the procedure fails at "setitsbackground colortotheColors'sitemv"
I've changed line 1 from 4 to 6, as I had simplified the example.
I should also say that it did color the first line (gray, albeit a different shade) but doesn't move on.
I tried with several other colors {"yellow", "red", "orange", "blue"}, etc and it does the same thing, just changes the first row (row 2) but fails to loop. I changed the loop from repeat with i from 2 to countt'srows-- from 2 to skip Header Row " to "...from 2 to 30" but still fails:
this is the error:
error "Can’t get item \"\" of {\"yellow\", \"gray\", \"orange\", \"blue\"}." number -1728 from item "" of {"yellow", "gray", "orange", "blue"}
Not sure what is different in your setup (the script works fine on my test table).
Try this, changing testCol to 6 or whatever column you are using.
The stock "yellow","gray","orange" ApplesCript colors will look different from the shades in Conditional Highlighting. These can be changed to whatever you want. But first suggest getting the script working before worrying about that.
SG
set testCol to 4 -- column D
set theKeys to {"1", "2", "3", "4"}
-- have a key for every possible value in testCol, surrounded by quotes
set theColors to {"yellow", "gray", "orange", "blue"}
-- order here is significant: yellow if value is 1, gray if 2, etc. -- and have a color for every possible value in D
tell application "Numbers"
tell document 1 to tell active sheet
set t to first table whose class of selection range is range
repeat with i from 2 to count t's rows -- from 2 to skip Header Row
tell t's row i
set v to its cell testCol's formatted value
if v is in theKeys then
set its background color to theColors's item v
end if
end tell
end repeat
end tell
end tell
Success, thanks very much.
Still not sure why the first script fails but at least I've learnt much about AppleScript and will be making use of this in the future.
This solves the issue I posted.
Glad you got it working.
I think the reason the first script failed is because you have formulas in column F whereas I was testing with values I had input manually. The formatted value of a cell with a formula is not missing value even if the result displays as a blank. So the script was trying to look up an item with a blank ("") number in theColors.
SG
fill format same as another cell
