Function to change cell fill based on text
I have a table, where I insert HEX color designation, and manually change to fill of a cell in a second row to said HEX color. Is there a way to do it automatically?
MacBook Pro
You can make a difference in the Apple Support Community!
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
I have a table, where I insert HEX color designation, and manually change to fill of a cell in a second row to said HEX color. Is there a way to do it automatically?
MacBook Pro
And this code was tested with Pages v12.0 on macOS 11.6.5. Here, I have no adjustment needed for column headings:
The AppleScript for this Pages solution:
use scripting additions
property hexList : "0123456789ABCDEF"
tell application "Pages"
tell first table of front document
set n to count of column "A"'s cell's value
repeat with i from 1 to n
set cellhex to "#" & column "A"'s cell i's value
set thisRange to (("B" & i as text) & ":" & "B" & i as text)
try
set background color of range thisRange to my Hex8toRGB16(cellhex)
end try
end repeat
end tell
end tell
return
to Hex8toRGB16(htmColor) -- format #xxxxxx
# courtesy of StefanK
# https://macscripter.net/viewtopic.php?id=24076
set RGB16 to {}
repeat with i from 2 to 6 by 2
set end of RGB16 to ((((offset of htmColor's character i in hexList) - 1) * 16) + (offset of htmColor's character (i + 1) in hexList) - 1) * 257
end repeat
return RGB16
end Hex8toRGB16
And this code was tested with Pages v12.0 on macOS 11.6.5. Here, I have no adjustment needed for column headings:
The AppleScript for this Pages solution:
use scripting additions
property hexList : "0123456789ABCDEF"
tell application "Pages"
tell first table of front document
set n to count of column "A"'s cell's value
repeat with i from 1 to n
set cellhex to "#" & column "A"'s cell i's value
set thisRange to (("B" & i as text) & ":" & "B" & i as text)
try
set background color of range thisRange to my Hex8toRGB16(cellhex)
end try
end repeat
end tell
end tell
return
to Hex8toRGB16(htmColor) -- format #xxxxxx
# courtesy of StefanK
# https://macscripter.net/viewtopic.php?id=24076
set RGB16 to {}
repeat with i from 2 to 6 by 2
set end of RGB16 to ((((offset of htmColor's character i in hexList) - 1) * 16) + (offset of htmColor's character (i + 1) in hexList) - 1) * 257
end repeat
return RGB16
end Hex8toRGB16
I wrote this for Numbers, and would still need to adapt it for a Pages table. If this is a Numbers sheet with a two-column table entry that looks like this:
I have put together an AppleScript that will convert the hex html strings to AppleScript's RGB16 colors to fill in the adjacent column:
The document must be open in Numbers, and then you run the following AppleScript from the Script Editor. Note that I am skipping the headings:
# AppleScript RGB color is 16-bit RGB in the form of {0,0,0} > {65535, 65535, 65535}
# which forces a html hex string conversion to get the correct cell fill colors
use scripting additions
property hexList : "0123456789ABCDEF"
tell application "Numbers"
tell first table of active sheet of front document
# implicitly ignores empty cells (e.g. missing value)
set n to count of column "A"'s cell's value
repeat with i from 2 to n
set cellhex to "#" & column "A"'s cell i's value
set thisRange to (("B" & i as text) & ":" & "B" & i as text)
try
set background color of range thisRange to my Hex8toRGB16(cellhex)
end try
end repeat
end tell
end tell
return
to Hex8toRGB16(htmColor) -- format #xxxxxx
# courtesy of StefanK
# https://macscripter.net/viewtopic.php?id=24076
set RGB16 to {}
repeat with i from 2 to 6 by 2
set end of RGB16 to ((((offset of htmColor's character i in hexList) - 1) * 16) + (offset of htmColor's character (i + 1) in hexList) - 1) * 257
end repeat
return RGB16
end Hex8toRGB16
Thank you so much, this worked perfectly for me.
You are welcome.
Function to change cell fill based on text