Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Changing background colors of cells in mac numbers

I have numbers files with knitting patterns. Basically, each cell has a value from 1-5. Each number corresponds to a different color. I would like to assign each number a hex color value and then have an applescript that will change the backgound of the cell and the number to that color. I can then change the color palette by changing the hex palette and see how the knitting changes. Ultimately, I would like to end up wih a file that looks something like below. Thank you very much

MacBook Pro 13″, macOS 12.2

Posted on Mar 13, 2022 6:30 PM

Reply
Question marked as Top-ranking reply

Posted on Mar 14, 2022 7:15 AM

Here's a basic script solution that should do what you want.


I tested it on a simplified table that looks like this.




The script:


-- insert desired hex colors associated with each code value in a cell between  the ("  ") -  
-- click in table first, then run

tell application "Numbers"
	tell active sheet of front document
		tell (first table whose class of selection range is range)
			set background color of cells whose value is 1 to my hexColorToRGB("#143C23")
			set background color of cells whose value is 2 to my hexColorToRGB("#20A603")
			set background color of cells whose value is 3 to my hexColorToRGB("#E7000D")
			set background color of cells whose value is 4 to my hexColorToRGB("#B21D1A")
			set background color of cells whose value is 5 to my hexColorToRGB("#F5E09D")
		end tell
	end tell
end tell


-- handlers
property hexChrs : "0123456789abcdef"

on hexColorToRGB(h) -- convert 
	set h to text 2 thru -1 of h -- remove leading #
	if (count h) < 6 then my badColor(h)
	set rgbColor to {}
	try
		repeat with i from 1 to 6 by 2
			set end of rgbColor to ((my getHexVal(text i of h)) * 16 + (my getHexVal(text (i + 1) of h))) * 257
		end repeat
	end try
	if (count rgbColor) < 3 then my badColor(h)
	return rgbColor
end hexColorToRGB

on getHexVal(c)
	if c is not in hexChrs then error
	set text item delimiters to c
	return (count text item 1 of hexChrs)
end getHexVal

on badColor(n)
	display alert n & " is not a valid hex color" buttons {"OK"} cancel button "OK"
end badColor



  1. Copy-paste into Script Editor (in Applications > Utilities)
  2. Change the hex values in the five lines near the beginning of the script to suit.
  3. Make sure Script Editor is listed and checked at System Preferences > Security & Privacy > Privacy > Accessibility.
  4. Click in the table that has the codes 1 through 5 in the cells.
  5. Click the triangle <run> button in Script Editor.


Bonus. Below is a script that allows you to retrieve the hex value of the background color of a selected cell. So you can color a cell to taste using the inspector pane at the right, then retrieve the hex value of the cell and insert that in the script above in the line for the code (1 to 5) the you want to assume that color.


--get background hex color of a Numbers cell (if none returns 'missing value')
-- click a cell with a background color and run
tell application "Numbers" to tell front document
	set t to first table of active sheet whose class of selection range is range
	set selRangeName to name of selection range of t
	set bkgrRgb to background color of cell 1 of range selRangeName of t
	if bkgrRgb is missing value then error "Selected cell has no background color"
	set bkgrColorHex to my convertRGBColorToHexValue(bkgrRgb)
end tell

on convertRGBColorToHexValue(theRGBValues)
	set theHexList to {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "A", "B", "C", "D", "E", "F"}
	set theHexValue to ""
	repeat with a from 1 to count of theRGBValues
		set theCurrentRGBValue to (item a of theRGBValues) div 256
		if theCurrentRGBValue is 256 then set theCurrentRGBValue to 255
		set theFirstItem to item ((theCurrentRGBValue div 16) + 1) of theHexList
		set theSecondItem to item (((theCurrentRGBValue / 16 mod 1) * 16) + 1) of theHexList
		set theHexValue to (theHexValue & theFirstItem & theSecondItem) as string
	end repeat
	set theHexValue to "#" & theHexValue
	set the clipboard to theHexValue
	return theHexValue -- to display in Script Editor
end convertRGBColorToHexValue



I've found that the easiest way to compare before and after colors is to duplicate the original table (click, then click the "bulls-eye" concentric circles upper left, command-c to copy, click the canvas on the sheet, and command-v to paste). Then click in that duplicated table before running the first script above (after changing the hex values in it to the new colors).


SG






Similar questions

4 replies
Sort By: 
Question marked as Top-ranking reply

Mar 14, 2022 7:15 AM in response to snk29970

Here's a basic script solution that should do what you want.


I tested it on a simplified table that looks like this.




The script:


-- insert desired hex colors associated with each code value in a cell between  the ("  ") -  
-- click in table first, then run

tell application "Numbers"
	tell active sheet of front document
		tell (first table whose class of selection range is range)
			set background color of cells whose value is 1 to my hexColorToRGB("#143C23")
			set background color of cells whose value is 2 to my hexColorToRGB("#20A603")
			set background color of cells whose value is 3 to my hexColorToRGB("#E7000D")
			set background color of cells whose value is 4 to my hexColorToRGB("#B21D1A")
			set background color of cells whose value is 5 to my hexColorToRGB("#F5E09D")
		end tell
	end tell
end tell


-- handlers
property hexChrs : "0123456789abcdef"

on hexColorToRGB(h) -- convert 
	set h to text 2 thru -1 of h -- remove leading #
	if (count h) < 6 then my badColor(h)
	set rgbColor to {}
	try
		repeat with i from 1 to 6 by 2
			set end of rgbColor to ((my getHexVal(text i of h)) * 16 + (my getHexVal(text (i + 1) of h))) * 257
		end repeat
	end try
	if (count rgbColor) < 3 then my badColor(h)
	return rgbColor
end hexColorToRGB

on getHexVal(c)
	if c is not in hexChrs then error
	set text item delimiters to c
	return (count text item 1 of hexChrs)
end getHexVal

on badColor(n)
	display alert n & " is not a valid hex color" buttons {"OK"} cancel button "OK"
end badColor



  1. Copy-paste into Script Editor (in Applications > Utilities)
  2. Change the hex values in the five lines near the beginning of the script to suit.
  3. Make sure Script Editor is listed and checked at System Preferences > Security & Privacy > Privacy > Accessibility.
  4. Click in the table that has the codes 1 through 5 in the cells.
  5. Click the triangle <run> button in Script Editor.


Bonus. Below is a script that allows you to retrieve the hex value of the background color of a selected cell. So you can color a cell to taste using the inspector pane at the right, then retrieve the hex value of the cell and insert that in the script above in the line for the code (1 to 5) the you want to assume that color.


--get background hex color of a Numbers cell (if none returns 'missing value')
-- click a cell with a background color and run
tell application "Numbers" to tell front document
	set t to first table of active sheet whose class of selection range is range
	set selRangeName to name of selection range of t
	set bkgrRgb to background color of cell 1 of range selRangeName of t
	if bkgrRgb is missing value then error "Selected cell has no background color"
	set bkgrColorHex to my convertRGBColorToHexValue(bkgrRgb)
end tell

on convertRGBColorToHexValue(theRGBValues)
	set theHexList to {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "A", "B", "C", "D", "E", "F"}
	set theHexValue to ""
	repeat with a from 1 to count of theRGBValues
		set theCurrentRGBValue to (item a of theRGBValues) div 256
		if theCurrentRGBValue is 256 then set theCurrentRGBValue to 255
		set theFirstItem to item ((theCurrentRGBValue div 16) + 1) of theHexList
		set theSecondItem to item (((theCurrentRGBValue / 16 mod 1) * 16) + 1) of theHexList
		set theHexValue to (theHexValue & theFirstItem & theSecondItem) as string
	end repeat
	set theHexValue to "#" & theHexValue
	set the clipboard to theHexValue
	return theHexValue -- to display in Script Editor
end convertRGBColorToHexValue



I've found that the easiest way to compare before and after colors is to duplicate the original table (click, then click the "bulls-eye" concentric circles upper left, command-c to copy, click the canvas on the sheet, and command-v to paste). Then click in that duplicated table before running the first script above (after changing the hex values in it to the new colors).


SG






Reply

Mar 13, 2022 8:49 PM in response to snk29970

snk29970 wrote:

Ultimately, I would like to end up wih a file that looks something like below.


Your image didn't make it into the forum software. To post a screenshot shift-command-4, select the area with the crosshairs, release, start post here and attach the screenshot image from the Desktop with the "mountain and moons" icon below the compose window.


With a little manipulation of the underlying encoded string I was able to produce this image.




Is that what you intended?


SG

Reply

Changing background colors of cells in mac numbers

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.