Hi
I have a largish table that contains some columns that contain dates from 1 Jan to 31 Dec.
I wish to change the text colour of weekends to Red. I cannot do this easily with conditional highlighting.
So I run a little script to do it. And it runs fine too except that every time the text is changed it takes a long time as if the document is recalculating every time. Going through the cells of 1 column takes some minutes.
Is there any way to speed this up
display dialog "Please input Year" default answer year of (current date)
set Yr to text returned of result as integer
tell application "Numbers" to tell table 1 of sheet 1 of document 1
set cl to (Yr - 2013) * 6 + 2 -- sets the column of the year in question
repeat with c from 4 to 369 -the rows containing dates
set d to value of cell c of column cl
if (weekday of d = Sunday) or (weekday of d = Saturday) then
set text color of cell c of column cl to {65535, 0, 0} -- Red
end if
end repeat
end tell
Doing it with conditional formatting is really easy.
You just need to ask the rule to apply red text to cells whose text contain “Sunday” or “Saturday”.
If you really want to use a script, You may try :
display dialog "Please input Year" default answer year of (current date)
set Yr to text returned of result as integer
set cl to (Yr - 2013) * 6 + 2 -- sets the column of the year in question
tell application "Numbers" to tell table 1 of sheet 1 of document 1
tell column cl
repeat with c from 3 to 21 --the rows containing dates
tell cell c
if (weekday of (get its value) is in {Saturday, Sunday}) then
set its text color to {65535, 0, 0} -- Red
end if
end tell # cell c
end repeat
end tell # column cl
end tell # Numbers
Yvan KOENIG running Sierra 10.12.1 in French (VALLAURIS, France) lundi 7 novembre 2016 20:21:54
Thank you for your reply.
First of all I’d like to say that I would prefer to use conditional highlighting instead of a script. I only used the script because I could not do it otherwise.
The columns contain dates (e.g. “18/11/2017 00:00:00”) without the weekday and are formatted to display only the day (e.g. 18). So I do not know how to set a rule as you suggested.
Regarding the script: your code is much neater, but it still takes 5.5 minutes to run through 365 cells. That seems rather slow don’t you think?
Once again, thank you.
Hi.
This should be a little faster:
display dialog "Please input Year" default answer year of (current date)
set Yr to text returned of result as integer
set cl to (Yr - 2013) * 6 + 2 -- sets the column of the year in question
tell application "Numbers" to tell table 1 of sheet 1 of document 1
tell column cl
set theDates to value of cells 1 thru 369 -- Read the cells en masse instead of individually.
repeat with i from 4 to 369 -- the list items containing the dates.
set w to weekday of item i of theDates
if ((w is Saturday) or (w is Sunday)) then set text color of cell i to {65535, 0, 0} -- or: if (w mod 6 is 1) . :)
end repeat
end tell # column cl
end tell # document 1
Hi Nigel
Your method took 2 minutes instead of 5.5 minutes.
Awesome!
Thank you.
Hi Pierre. Glad it helped.
If you have several entries for each weekend (ie. if weekend dates occur in runs), a further development would be to apply the colour once for each run instead of once for each date:
display dialog "Please input Year" default answer year of (current date)
set Yr to text returned of result as integer
set cl to (Yr - 2013) * 6 + 2 -- sets the column of the year in question
tell application "Numbers" to tell table 1 of sheet 1 of document 1
tell column cl
set theDates to value of cells 1 thru 369 -- Read the cells en masse instead of individually.
set weekendFlag to false
repeat with i from 4 to 369 -- the list items containing the dates.
set w to weekday of item i of theDates
if ((w is Saturday) or (w is Sunday)) then
if (weekendFlag is false) then -- First date in a weekend run.
set weekendFlag to true
set runStart to i
end if
else if (weekendFlag) then -- First date after a weekend run.
set weekendFlag to false
set text color of cells runStart thru (i - 1) to {65535, 0, 0} -- Colour the run.
end if
end repeat
-- If a run was in progress when the repeat ended, colour it.
if (weekendFlag) then set text color of cells runStart thru i to {65535, 0, 0}
end tell # column cl
end tell # document 1
Thank you for that idea.
There is only one entry for each weekend.
Actually I only need to check for Saturday, as Sunday will always follow (except maybe the last cell).
I modified your idea as follows:
display dialog "Please input Year" default answer year of (current date)
set Yr to text returned of result as integer
set cl to (Yr - 2013) * 6 + 2
tell application "Numbers" to tell table 1 of sheet "Meters" of document "PV Generation"
tell column cl
set theDates to value of cells 1 thru 369 -- Read the cells en masse instead of individually.
repeat with i from 4 to 368 -- stops 1 cell short
set w to weekday of item i of theDates
if (w is Saturday) then set text color of cells i thru (i + 1) to {65535, 0, 0}
end repeat
set w to weekday of last item of theDates
if ((w is Saturday) or (w is Sunday)) then set text color of last cell to {65535, 0, 0} -- handles last cell
end tell
end tell