Convert Conditional Formatting to Static Formatting

considering Keynote is for presentations, you cant be placing too much data in a single slide, hand coloring is out of the question?

Jason

TK321 wrote:
Is there a way to convert conditional text formatting to static text formatting?

What I am trying to do is this:
I formatted a large spreadsheet with conditional formatting in order to color-code data in the cells.

I'm puzzled.
When I copy such a table from Numbers then paste it in Keynote, the conditional formats are passed.
I checked with two schemes :

(1) I copied a range of cells and pasted in Keynote
(2) I selected the table's thumbnail, copy it, paste in Keynote.

In both cases everything was correctly passed.

I am unable to guess what you made wrongly.

Yvan KOENIG (VALLAURIS, France) vendredi 8 avril 2011 23:00:51

Keynote on the iPad, like Numbers on the iPad, probably does not support conditional formatting. Pasting a Numbers table into Keynote keeps the conditional formatting rules in the table, as can be seen when you change the value in a cell and the formatting changes. This is, of course, the cause of the problem.

If you are not making changes to the table in Keynote, you simply want an image of the table, print your table (and whatever else gets printed along with it) but, in the Print window, choose Open PDF in Preview. In Preview, select the area you want for your Keynote presentation, Copy, then paste it into Keynote and resize it as desired.

As for the original questions, I do not know how to lock in a conditional format other than going cell to cell and setting the background color of each to match the conditional format then deleting the conditional formats.

TK321 wrote:
Right - I knew the problem was the limitation in the iPad app - that's why I was trying to find a way to convert the formatting to something that would transfer with a copy paste.

Fixed formatting will transfer with copy/paste.

I was thinking maybe a formula would work, or maybe using a formula would allow the formatting to survive a paste to a third party app, without relying on the conditional format support, and then into Keynote...

(The original) Conditional formatting depends on a condition being met. If the formatting is to survive without reference to the condition, then it is not 'conditional formatting.' Whatever 'base' (fixed) formatting was applied will apply after the transfer if the receiving application does not support conditional formatting. If you want the 'conditional' formatting to apply after the transfer, then it must be made into the 'fixed' formatting before the transfer takes place.

Regards,
Barry

--

--[SCRIPT conditionalto_staticformat]
(*
Enregistrer le script en tant que Script : conditionalto_staticformat.scpt
déplacer le fichier ainsi créé dans le dossier
<VolumeDeDémarrage>:Users:<votreCompte>:Library:Scripts:Applications:Numbers:
Il vous faudra peut-être créer le dossier Numbers et peut-être même le dossier Applications.
Sélectionner le groupe de cellules à traiter.
Aller au menu Scripts , choisir Numbers puis choisir “conditionalto_staticformat”
Le script récupère les propriétés couleur de fond, couleur de texte, nom de police
des cellules sélectionnées.
Il copie les cellules puis applique "Coller les valeurs" ce qui détruit le format conditionnel.
Il applique ensuite les propriétés préalablement extraites.
En raison d'une anomalie les informations passées à AppleScript sont erronées :
la couleur de texte récupérée est toujours le noir
le nom de police récupéré est le nom de la police standard même si un style est appliqué.
En conséquence j'ai prévu une "property" qui désactive quelques actions.
J'ai rédigé un rapport de bug afin qu'Apple corrige le programme.
--=====
L’aide ** Finder explique:
L’Utilitaire AppleScript permet d’activer le Menu des scripts :
Ouvrez l’Utilitaire AppleScript situé dans le dossier Applications/AppleScript.
Cochez la case “Afficher le menu des scripts dans la barre de menus”.
Sous 10.6.x,
aller dans le panneau “Général” ** dialogue Préférences de l’Éditeur Applescript
puis cocher la case “Afficher le menu des scripts dans la barre des menus”.
--=====
Save the script as a Script: conditionalto_staticformat.scpt
Move the newly created file into the folder:
<startup Volume>:Users:<yourAccount>:Library:Scripts:Applications:Numbers:
Maybe you would have to create the folder Numbers and even the folder Applications by yourself.
Select the range of cells to treat.
Go to the Scripts Menu, choose Numbers, then choose “conditionalto_staticformat”
The script extract the properties : background color, text color and font name
of the selected cells.
It copy the cells then apply "Paste Values" to kill the conditional format.
Then it apply the properties which it extracted before.
There is a bug which clobber the extraction of properties :
the passed text color is always black
the passed font name is always the bare one, even when a style is applied.
So, I defined a property which disable some actions which at this time are useless.
I wrote a bug report asking Apple to kill the bug.
--=====
The Finder’s Help explains:
To make the Script menu appear:
Open the AppleScript utility located in Applications/AppleScript.
Select the “Show Script Menu in menu bar” checkbox.
Under 10.6.x,
go to the General panel of AppleScript Editor’s Preferences dialog box
and check the “Show Script menu in menu bar” option.
--=====
Yvan KOENIG (VALLAURIS, France)
2011/04/09
*)
--=====
property bug_repaired : false
(*
If Apple correct the described bug, it would be easy to set the property to true
*)
--=====
on run
 local dName, sName, tName, rowNum1, colNum1, rowNum2, colNum2, lescouleurs_defond
 local lescouleurs_detexte, rc, r, lignecouleurs_defond, lignecouleurs_detexte, cc, C
 
 my activateGUIscripting()
 (*
Grab the coordinates of cells to treat
*)
 set {dName, sName, tName, rowNum1, colNum1, rowNum2, colNum2} to my get_SelParams()
 (*
Grab the colors and font of every selected cells
*)
 tell application "Numbers" to tell document dName to tell sheet sName to tell table tName
 set lescouleurs_defond to {}
 if bug_repaired then
 set lescouleurs_detexte to {}
 set lesnoms_depolice to {}
 end if
 repeat with r from rowNum1 to rowNum2
 set lignecouleurs_defond to {}
 if bug_repaired then
 set lignecouleurs_detexte to {}
 set lignenoms_depolice to {}
 end if
 repeat with C from colNum1 to colNum2
 copy background color of cell C of row r to end of lignecouleurs_defond
 if bug_repaired then
 copy text color of cell C of row r to end of lignecouleurs_detexte
 copy font name of cell C of row r to end of lignenoms_depolice
 end if
 end repeat
 copy lignecouleurs_defond to end of lescouleurs_defond
 if bug_repaired then
 copy lignecouleurs_detexte to end of lescouleurs_detexte
 copy lignenoms_depolice to end of lesnoms_depolice
 end if
 end repeat
 (*
Copy the selected cells then Paste Values to kill the conditional format
*)
 set selection range to range (name of cell rowNum1 of column colNum1 & ":" & name of cell rowNum2 of column colNum2)
 my raccourci("Numbers", "x", "c") (* Cut *)
 my selectMenu("Numbers", 4, 8) (* Edit > Paste Values *)
 (*
Apply the original colors and font.
*)
 set rc to 1
 repeat with r from rowNum1 to rowNum2
 set lignecouleurs_defond to item rc of lescouleurs_defond
 if bug_repaired then
 set lignecouleurs_detexte to item rc of lescouleurs_detexte
 set lignenoms_depolice to item rc of lesnoms_depolice
 end if
 set cc to 1
 repeat with C from colNum1 to colNum2
 set background color of cell C of row r to item cc of lignecouleurs_defond
 if bug_repaired then
 set text color of cell C of row r to item cc of lignecouleurs_detexte
 set font name of cell C of row r to item cc of lignenoms_depolice
 end if
 set cc to cc + 1
 end repeat
 set rc to rc + 1
 end repeat
 
 end tell
end run
--=====
(*
set { dName, sName, tName, rowNum1, colNum1, rowNum2, colNum2} to my get_SelParams()
*)
on get_SelParams()
 local d_name, s_name, t_name, row_num1, col_num1, row_num2, col_num2
 tell application "Numbers" to tell document 1
 set d_name to its name
 set s_name to ""
 repeat with i from 1 to the count of sheets
 tell sheet i to set maybe to the count of (tables whose selection range is not missing value)
 if maybe is not 0 then
 set s_name to name of sheet i
 exit repeat
 end if -- maybe is not 0
 end repeat
 if s_name is "" then
 if my parleAnglais() then
 error "No sheet has a selected table embedding at least one selected cell !"
 else
 error "Aucune feuille ne contient une table ayant au moins une cellule sélectionnée !"
 end if
 end if
 tell sheet s_name to tell (first table where selection range is not missing value)
 tell selection range
 set {top_left, bottom_right} to {name of first cell, name of last cell}
 end tell
 set t_name to its name
 tell cell top_left to set {row_num1, col_num1} to {address of its row, address of its column}
 if top_left is bottom_right then
 set {row_num2, col_num2} to {row_num1, col_num1}
 else
 tell cell bottom_right to set {row_num2, col_num2} to {address of its row, address of its column}
 end if
 end tell -- sheet…
 return {d_name, s_name, t_name, row_num1, col_num1, row_num2, col_num2}
 end tell -- Numbers
end get_SelParams
--=====
on parleAnglais()
 local z
 try
 tell application "Numbers" to set z to localized string "Cancel"
 on error
 set z to "Cancel"
 end try
 return (z is not "Annuler")
end parleAnglais
--=====
on activateGUIscripting()
 (* to be sure than GUI scripting will be active *)
 tell application "System Events"
 if not (UI elements enabled) then set (UI elements enabled) to true
 end tell
end activateGUIscripting
--=====
(*
==== Uses GUIscripting ==== 
*)
(*
This handler may be used to 'type' text, invisible characters if the third parameter is an empty string. 
It may be used to 'type' keyboard raccourcis if the third parameter describe the required modifier keys. 
I changed its name « shortcut » to « raccourci » to get rid of a name conflict in Smile. 
*)
on raccourci(a, t, d)
 local k
 tell application a to activate
 tell application "System Events" to tell application process a
 set frontmost to true
 try
 t * 1
 if d is "" then
 key code t
 else if d is "c" then
 key code t using {command down}
 else if d is "a" then
 key code t using {option down}
 else if d is "k" then
 key code t using {control down}
 else if d is "s" then
 key code t using {shift down}
 else if d is in {"ac", "ca"} then
 key code t using {command down, option down}
 else if d is in {"as", "sa"} then
 key code t using {shift down, option down}
 else if d is in {"sc", "cs"} then
 key code t using {command down, shift down}
 else if d is in {"kc", "ck"} then
 key code t using {command down, control down}
 else if d is in {"ks", "sk"} then
 key code t using {shift down, control down}
 else if (d contains "c") and (d contains "s") and d contains "k" then
 key code t using {command down, shift down, control down}
 else if (d contains "c") and (d contains "s") and d contains "a" then
 key code t using {command down, shift down, option down}
 end if
 on error
 repeat with k in t
 if d is "" then
 keystroke (k as text)
 else if d is "c" then
 keystroke (k as text) using {command down}
 else if d is "a" then
 keystroke k using {option down}
 else if d is "k" then
 keystroke (k as text) using {control down}
 else if d is "s" then
 keystroke k using {shift down}
 else if d is in {"ac", "ca"} then
 keystroke (k as text) using {command down, option down}
 else if d is in {"as", "sa"} then
 keystroke (k as text) using {shift down, option down}
 else if d is in {"sc", "cs"} then
 keystroke (k as text) using {command down, shift down}
 else if d is in {"kc", "ck"} then
 keystroke (k as text) using {command down, control down}
 else if d is in {"ks", "sk"} then
 keystroke (k as text) using {shift down, control down}
 else if (d contains "c") and (d contains "s") and d contains "k" then
 keystroke (k as text) using {command down, shift down, control down}
 else if (d contains "c") and (d contains "s") and d contains "a" then
 keystroke (k as text) using {command down, shift down, option down}
 end if
 end repeat
 end try
 end tell
end raccourci
--=====
(*
my selectMenu("Pages",5, 12)
==== Uses GUIscripting ====
*)
on selectMenu(theApp, mt, mi)
 tell application theApp
 activate
 tell application "System Events" to tell process theApp to tell menu bar 1 to ¬
 tell menu bar item mt to tell menu 1 to click menu item mi
 end tell -- application theApp
end selectMenu
--=====
(*
my selectSubMenu("Pages",6, 4, 26)
==== Uses GUIscripting ====
*)
on selectSubMenu(theApp, mt, mi, ms)
 
 tell application theApp
 activate
 tell application "System Events" to tell process theApp to tell menu bar 1 to ¬
 tell menu bar item mt to tell menu 1 to tell menu item mi to tell menu 1 to click menu item ms
 end tell -- application theApp
end selectSubMenu
--=====
(*
useful to get the indexs of the triggered item
my select_Menu("Numbers", 4, 8) (* Edit > Paste Values *)
*)
on select_menu(theApp, mt, mi)
 tell application theApp
 activate
 tell application "System Events" to tell process theApp to tell menu bar 1
 get name of menu bar items
 (*{ 
 01 - "Apple", 
 02 - "Numbers", 
 03 - "Fichier", 
 04 - "Édition", 
 05 - "Insertion", 
 06 - "Tableau", 
 07 - "Format", 
 08 - "Disposition", 
 09 - "Présentation", 
 10 - "Fenêtre", 
 11 - "Partage", 
 12 - "Aide"}
 *)
 get name of menu bar item mt
 -- {"Insert"}
 tell menu bar item mt to tell menu 1
 get name of menu items
 (* 
 01 - "Annuler Supprimer les cellules", 
 02 - "Rétablir", 
 03 - missing value, 
 04 - "Couper", 
 05 - "Copier", 
 06 - "Coller", 
 07 - "Coller et appliquer le style", 
 08 - "Coller les valeurs", 
 09 - "Supprimer", 
 10 - "Supprimer les rangs", 
 11 - "Supprimer la colonne", 
 12 - "Tout supprimer", 
 13 - missing value, 
 14 - "Signaler pour être déplacé", 
 15 - "Déplacer", 
 16 - missing value, 
 17 - "Dupliquer", 
 18 - missing value, 
 19 - "Tout sélectionner", 
 20 - "Tout désélectionner", 
 21 - missing value, 
 22 - "Rechercher", 
 23 - "Orthographe", 
 24 - missing value, 
 25 - "Caractères spéciaux…"}
 *)
 get name of menu item mi
 --{"Coller les valeurs"}
 click menu item mi
 end tell
 
 end tell
 end tell -- application theApp
end select_menu
--=====
--[/SCRIPT]
--

Yvan KOENIG (VALLAURIS, France) samedi 9 avril 2011 12:22:28

At this time there is an other "bug". The Bug Report area is unreachable 😟

Yvan KOENIG (VALLAURIS, France) samedi 9 avril 2011 19:24:12

That is just plain funny.

At this time it's always unreachable.
Not so funny 😟

Yvan KOENIG (VALLAURIS, France) dimanche 10 avril 2011 09:51:37