Conditional Highlighting

When working with tables, a standard technique for indicating trends and spikes is through the use of conditional highlighting or “heat mapping”. This feature is built into the table tools interface for all of the iWork applications, and allows you to indicate the text and background coloring of cells based upon their contained values.

A table displaying rows and columns of percentage values.

(⬆ see above ) A table displaying rows and columns of percentage values.
(⬇ see below ) The same table as shown in the image above, but with the background and text color of the cells adjusted depending upon their numeric value. (heat mapping)

The same table as shown in the image above, but with the background and text color of the cells adjusted depending upon their numeric value.

The following scripts can be used to demonstrate conditional highlighting. The first script will generate a table with random percentage values in a new Keynote document. The second script will highlight the selected cells of a table where the cell uses percent formatting. Fun!

Create Table of Random Percentage Values
  
01set the columnCount to 8
02set the rowCount to 10
03set the headerRowCount to 0
04set the headerColumnCount to 0
05set the footerRowCount to 0
06 
07tell application "Keynote"
08 activate
09 
10 set thisDocument to ¬
11 make new document with properties ¬
12 {document theme:theme "Black", width:1024, height:768}
13 
14 tell thisDocument
15 tell current slide
16 set base slide to master slide "Blank" of thisDocument
17 set thisTable to ¬
18 make new table with properties ¬
19 {column count:columnCount ¬
20 , row count:rowCount ¬
21 , footer row count:footerRowCount ¬
22 , header column count:headerColumnCount ¬
23 , header row count:headerRowCount}
24 tell thisTable
25 set format of cell range to percent
26 repeat with i from 1 to the count of cells
27 set the value of cell i to ((random number from 0 to 100) * 0.01)
28 end repeat
29 end tell
30 end tell
31 end tell
32end tell
Apply Conditional Highlighting to Selected Cells
  
01tell application "Keynote"
02 activate
03 try
04 if not (exists document 1) then error number 1000
05 tell document 1
06 try
07 tell current slide
08 set the selectedTable to ¬
09 (the first table whose class of selection range is range)
10 end tell
11 on error
12 error number 1001
13 end try
14 tell selectedTable
15 set selectionRangeID to the name of the selection range
16 set theseCellIDs to the name of every cell of the selection range
17 set cellCount to the count of theseCellIDs
18 repeat with i from 1 to the cellCount
19 set thisCell to cell (item i of theseCellIDs)
20 if format of thisCell is percent then
21 set thisValue to ((value of thisCell) * 100) as integer
22 copy my getHighlightingColorsFor(thisValue) to ¬
23 {thisTextColor, thisBackgroundColor}
24 tell thisCell
25 set text color to thisTextColor
26 set background color to thisBackgroundColor
27 end tell
28 end if
29 end repeat
30 set selection range to range selectionRangeID
31 end tell
32 end tell
33 on error errorMessage number errorNumber
34 if errorNumber is 1000 then
35 set alertString to "MISSING RESOURCE"
36 set errorMessage to "Please create or open a document before running this script."
37 else if errorNumber is 1001 then
38 set alertString to "SELECTION ERROR"
39 set errorMessage to "Please select a table before running this script."
40 else
41 set alertString to "EXECUTION ERROR"
42 end if
43 display alert alertString message errorMessage buttons {"Cancel"}
44 error number -128
45 end try
46end tell
47 
48on getHighlightingColorsFor(thisValue)
49 if thisValue is greater than or equal to 100 then
50 return {{65535, 65535, 65535}, {13822, 5295, 2341}}
51 else if thisValue is greater than or equal to 80 then
52 return {{65535, 65535, 65535}, {27643, 10590, 4681}}
53 else if thisValue is greater than or equal to 60 then
54 return {{65535, 65535, 65535}, {41465, 15885, 7022}}
55 else if thisValue is greater than or equal to 40 then
56 return {{0, 0, 0}, {54873, 21393, 9792}}
57 else if thisValue is greater than or equal to 20 then
58 return {{0, 0, 0}, {57539, 32428, 23728}}
59 else if thisValue is greater than or equal to 0 then
60 return {{0, 0, 0}, {60204, 43464, 37664}}
61 end if
62end getHighlightingColorsFor

TOP | CONTINUE