Copy link to clipboard
Copied
Hi all, I have this nice script created with the help of the amazing community.
I need to update it, so that when for example a cell changes into red the cell font color also changes but to white to ensure readability. Can anyone let me know what I need to add in?
app.doScript(main, ScriptLanguage.JAVASCRIPT, undefined, UndoModes.ENTIRE_SCRIPT, "Conditional cell formatting");
function main() {
sel = app.selection[0];
var cells = sel.cells
// create color swatches
var red = swatchCreator("Red", [210,35,42]);
var yellow = swatchCreator("Amber", [0,51,99,0]);
var green = swatchCreator("Green", [69,8,87,0]);
for (var c = 0; c < cells.length; c++) {
var cc = cells[c].texts[0].contents.split("%")
var cellValue = parseInt(cc[0])
isNaN(cellValue) && cellValue = 0
if (cellValue < 80) {
cells[c].fillColor = red;
continue;
}
if (cellValue >= 80 && cellValue < 95) {
cells[c].fillColor = yellow;
continue;
}
if (cellValue >= 95) {
cells[c].fillColor = green;
}
}
}
function swatchCreator(colorname, values) {
var swatch = app.activeDocument.colors.item(colorname);
if (!swatch.isValid) {
swatch = app.activeDocument.colors.add({
name: colorname,
model: ColorModel.PROCESS,
space: ColorSpace.CMYK,
colorValue: values
});
}
return swatch;
}
Change the following
if (cellValue < 80) {
cells[c].fillColor = red;
continue;
}
to
if (cellValue < 80) {
cells[c].fillColor = red;
cells[c].texts[0].fillColor = "Paper"
continue;
}
I have used color Paper, if you need to create your own swatch do it as the other colors are created.
-Manan
Try the following
app.doScript(main, ScriptLanguage.JAVASCRIPT, undefined, UndoModes.ENTIRE_SCRIPT, "Conditional cell formatting");
function main() {
sel = app.selection[0];
var cells = sel.cells
// create color swatches
var black = addBlackTint()
var red = swatchCreator("Red", [0,100,100,0]);
var yellow = swatchCreator("Amber", [0,51,99,0]);
var green = swatchCreator("Green", [69,8,87,0]);
for (var c = 0; c < cells.length; c++) {
if(cells[c].contents == "
...
Copy link to clipboard
Copied
Hi again.
Also, what should I script if a cell with no data in it needs to be in black with 20% tint?
Copy link to clipboard
Copied
Change the following
if (cellValue < 80) {
cells[c].fillColor = red;
continue;
}
to
if (cellValue < 80) {
cells[c].fillColor = red;
cells[c].texts[0].fillColor = "Paper"
continue;
}
I have used color Paper, if you need to create your own swatch do it as the other colors are created.
-Manan
Copy link to clipboard
Copied
Thank you for your help Manan, it worked perfectly.
And if I want to change those cells with no value to Black with 20%ink?
Cheers
Copy link to clipboard
Copied
Try the following
app.doScript(main, ScriptLanguage.JAVASCRIPT, undefined, UndoModes.ENTIRE_SCRIPT, "Conditional cell formatting");
function main() {
sel = app.selection[0];
var cells = sel.cells
// create color swatches
var black = addBlackTint()
var red = swatchCreator("Red", [0,100,100,0]);
var yellow = swatchCreator("Amber", [0,51,99,0]);
var green = swatchCreator("Green", [69,8,87,0]);
for (var c = 0; c < cells.length; c++) {
if(cells[c].contents == "")
{
cells[c].fillColor = black;
continue;
}
var cc = cells[c].texts[0].contents.split("%")
var cellValue = parseInt(cc[0])
isNaN(cellValue) && cellValue = 0
if (cellValue < 80) {
cells[c].fillColor = red;
cells[c].texts[0].fillColor = "Paper"
continue;
}
if (cellValue >= 80 && cellValue < 95) {
cells[c].fillColor = yellow;
continue;
}
if (cellValue >= 95) {
cells[c].fillColor = green;
}
}
}
function swatchCreator(colorname, values) {
var swatch = app.activeDocument.colors.item(colorname);
if (!swatch.isValid) {
swatch = app.activeDocument.colors.add({
name: colorname,
model: ColorModel.PROCESS,
space: ColorSpace.CMYK,
colorValue: values
});
}
return swatch;
}
function addBlackTint()
{
var tint = app.activeDocument.tints.item("Black 20");
if (!tint.isValid) {
tint = app.activeDocument.tints.add(app.activeDocument.colors.itemByName("Black"), {
name: "Black 20",
tintValue: 20
});
}
return tint
}
-Manan
Copy link to clipboard
Copied
Worked like a charm.
Thanks!