• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
1

Is it possible to write a script in InDesign to do conditional formatting in table cells?

Community Beginner ,
Aug 14, 2019 Aug 14, 2019

Copy link to clipboard

Copied

I have lots of data heavy excel documents that use conditional formatting to automatically colour a cell depending on the value of its contents.

IE- Green for 0-50, white for 51-150 Red for over 150.

I am copy this data into a design doc in InDesign. I would like to use the same conditional formatting in Indesign so I don't have to colour all cells by hand. I have been told that this is not a native feature in InDesign but it could be possible to write a script for this. Could anyone help with this?

I have a digital team that could do this for me but we need some more information. Has anyone done this before?

Thanks!

TOPICS
Scripting

Views

3.3K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Contributor , Aug 14, 2019 Aug 14, 2019

Hi,

you can try this one:

app.doScript(main, ScriptLanguage.JAVASCRIPT, undefined, UndoModes.ENTIRE_SCRIPT, "Conditional cell formatting");

function main() {

    sel = app.selection[0];

    if (sel.constructor.name != "TextFrame") {

        alert("Please select the textframe that contains the table.");

        exit();

    }

    // create color swatches

    var green = swatchCreator("Green", [75,0,100,0]);

    var red = swatchCreator("Red", [0,100,100,0]);

    var white = swatchCreator("White", [0,0,0,0]);

  

...

Votes

Translate

Translate
Contributor ,
Aug 14, 2019 Aug 14, 2019

Copy link to clipboard

Copied

Hi,

you can try this one:

app.doScript(main, ScriptLanguage.JAVASCRIPT, undefined, UndoModes.ENTIRE_SCRIPT, "Conditional cell formatting");

function main() {

    sel = app.selection[0];

    if (sel.constructor.name != "TextFrame") {

        alert("Please select the textframe that contains the table.");

        exit();

    }

    // create color swatches

    var green = swatchCreator("Green", [75,0,100,0]);

    var red = swatchCreator("Red", [0,100,100,0]);

    var white = swatchCreator("White", [0,0,0,0]);

    tables = sel.tables;

    for (var i = 0; i < tables.length; i++) {

        var cells = tables.cells;

       

        for (var c = 0; c < cells.length; c++) {

           

            var cellValue = parseInt(cells.texts[0].contents);

          

            if (cellValue <= 50) {

                cells.fillColor = green;

                continue;

            }

       

            if (cellValue >= 51 && cellValue <=150) {

                cells.fillColor = white;

                continue;

            }

       

            if (cellValue > 150) {

                cells.fillColor = red;

            }

        }

    }

}

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;

}

Select the textframe which contains the table(s) and run that script. It will create color swatches Green, Red, White. Then it changes the color of the cells.

conditional cell formatting.png

Regards

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Aug 19, 2019 Aug 19, 2019

Copy link to clipboard

Copied

Thanks so much for this.

Our digital team are looking at implementing it now, so you've been a huge help!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Nov 07, 2019 Nov 07, 2019

Copy link to clipboard

Copied

Hi, Thanks for sharing the script but unfortunately I got these two results even after minor tweaks in the code like, initially i have assigned fill color with combination of letters and numbers but later I have changed swatch name to blue, red and green but still problem persists. Please help me in resolving following errors.

 

 1.  Error Number: 25

Error String: Expected: ;

Engine: main

File path

Line

Source: Cell.fillColor = PROCESS 2748

Offending Text: 2748

 

 2.  Error Number: 21

Error String: Undefined is not an object

Engine: main

File path

Line

Source: app.doScript(main, ScriptLanguage.JAVASCRIPT, undefined, UndoModes.ENTIRE_SCRIPT, "Conditional cell formatting");

 

Following is the script I used to run in indesign

 

app.doScript(main, ScriptLanguage.JAVASCRIPT, undefined, UndoModes.ENTIRE_SCRIPT, "Conditional cell formatting"); function main() {

sel = app.selection[0];

if (sel.constructor.name != "TextFrame") {

alert("Please select the textframe that contains the table.");

exit();

}

// create color swatches

var green = swatchCreator("Blue", [100,88,0,14]);

var red = swatchCreator("Red", [0,100,2,0]);

var white = swatchCreator("Green", [76,0,38,0]);

tables = sel.tables;

for (var i = 0; i < tables.length; i++) {

var cells = tables.cells;

for (var c = 0; c < cells.length; c++) {

var cellValue = parseInt(cells.texts[0].contents);

if (cellValue <= 0.29) {

cells.fillColor = Blue;

continue;

}

if (cellValue >= 0.30 && cellValue <=0.69) {

cells.fillColor = Red;

continue;

}

if (cellValue > 1.00) {

cells.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;

}

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Nov 07, 2019 Nov 07, 2019

Copy link to clipboard

Copied

You did not carefully copy the original script. There is no variable named "Red", nor is there one called "Green". But you try to use them anyway.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Nov 08, 2019 Nov 08, 2019

Copy link to clipboard

Copied

Well actually if you look at the origianl script provided by crazy panda, you can see that there is name defined for color value and I did same by creating according to my requirement. 

 

Lets do one thing, just make me understand whether the script provided by crazy panda has any flaw or else it works fine. If not I hope you understand my requirement so could you please provide script for the same which I'm looking for.

 

Above all these could you please suggest me how to learn Java scripting which is required for this particular tool. I mean in simple way to suffice the purpose. I know its not easy, it has some prerequisite to learn the script, but still guide me in learning script. Thank you!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Nov 12, 2019 Nov 12, 2019

Copy link to clipboard

Copied

Does this script still work in ID2020?  I keep getting the following error message when run on a Mac OSX 10.14.6:

Screen Shot 2019-11-12 at 22.06.04.png

If the answer wasn't in my post, perhaps it might be on my blog at colecandoo!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Nov 12, 2019 Nov 12, 2019

Copy link to clipboard

Copied

Hi Colin,

what exactly did you select when running the script?

Hm. What's your exact code? Can you post it with code formatting of the forum?

 

Regards,
Uwe Laubender

( ACP )

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Nov 14, 2019 Nov 14, 2019

Copy link to clipboard

Copied

Hi Uwe.

 

I made a new InDesign document and made a single textframe with a single 4x4 table with exactly the same numbers as per crazyPanda's script. The text frame was then selected with the black arrow tool and the script was run. Here is everything that is in the script below:

 

app.doScript(main, ScriptLanguage.JAVASCRIPT, undefined, UndoModes.ENTIRE_SCRIPT, "Conditional cell formatting");

function main() {
   sel = app.selection[0];
   if (sel.constructor.name != "TextFrame") {
       alert("Please select the textframe that contains the table.");
       exit();
   }
   // create color swatches
   var green = swatchCreator("Green", [75,0,100,0]);
   var red = swatchCreator("Red", [0,100,100,0]);
   var white = swatchCreator("White", [0,0,0,0]);
   tables = sel.tables;
   for (var i = 0; i < tables.length; i++) {
       var cells = tables.cells;
       
       for (var c = 0; c < cells.length; c++) {
           
           var cellValue = parseInt(cells.texts[0].contents);
          
           if (cellValue <= 50) {
               cells.fillColor = green;
               continue;
           }
       
           if (cellValue >= 51 && cellValue <=150) {
               cells.fillColor = white;
               continue;
           }
       
           if (cellValue > 150) {
               cells.fillColor = red;
           }
       }
   }

}

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;

}

 In ESTK, the script doesn't like the line:

 

var cells = tables.cells;

 

reports the error message "Object does not support the property or method 'cells'.

If the answer wasn't in my post, perhaps it might be on my blog at colecandoo!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Nov 14, 2019 Nov 14, 2019

Copy link to clipboard

Copied

Hi Colin,

ah. That's just a typo I think.

Since you are iterating over the tables collection with variable i as counter correct this line to:

 

var cells = tables[i].cells;

 

And also in the next loop, the loop that is iterating the cells collection with variable c as counter, you should address a single cell with its index [c]:

var cellValue = parseInt(cells[c].texts[0].contents);

And of course also:

cells[c].fillColor = green;

 

cells[c].fillColor = white;

 

cells[c].fillColor = red;

 

Best,
Uwe Laubender

( ACP )

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Nov 15, 2019 Nov 15, 2019

Copy link to clipboard

Copied

Yep, that got it working.

 

Thank you Uwe!

If the answer wasn't in my post, perhaps it might be on my blog at colecandoo!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Aug 03, 2020 Aug 03, 2020

Copy link to clipboard

Copied

Any chance you could explain how to check for text instead? So if the cell contains an "A" the cell color would change to red.

 

Appreciate any help.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Nov 18, 2019 Nov 18, 2019

Copy link to clipboard

Copied

Hi Colin,

glad it's working now.

Ahem. That said, out of the same reasons crazyPanda's code in the reply, that is currently marked as "Correct", will not work.

 

Regards,
Uwe Laubender

( ACP )

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Feb 26, 2020 Feb 26, 2020

Copy link to clipboard

Copied

Hi, and thanks for sharing the script.

 

I try to use this script, I get no errors but it dosn't work on my computer...

 

Here is my script:

 

function main() {
sel = app.selection[0];
if (sel.constructor.name != "TextFrame") {
alert("Please select the textframe that contains the table.");
exit();
}
// create color swatches
var svert_bra = swatchCreator("svert_bra", [83,18,78,4]);
var veldig_bra = swatchCreator("veldig_bra", [53,2,99,0]);
var bra = swatchCreator("bra", [9,0,92,0]);
var mindre_bra = swatchCreator ("mindre_bra", [1,51,93,0]);
var svakt = swatchCreator ("svakt", [0,91,78,0])
tables = sel.tables;
for (var i = 0; i < tables.length; i++) {
var cells = tables.cells;

for (var c = 0; c < cells.length; c++) {

var cellValue = parseInt(cells.texts[0].contents);

if (cellValue >=5) {
cells.fillColor = svert_bra;
continue;
}

if (cellValue >= 4 && cellValue <=6) {
cells.fillColor = veldig_bra;
continue;
}

if (cellValue >= 3 && cellValue <=5) {
cells.fillColor = bra;
continue;
}

if (cellValue >= 2 && cellValue <= 4) {
cells.fillColor = mindre_bra;
continue;
}

if (cellValue >= 0 && cellValue <= 3) {
cells.fillColor = svakt;
}
}
}
}
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;
}

 

 

What is wrong with this one?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 26, 2020 Feb 26, 2020

Copy link to clipboard

Copied

Hi Thomas,

the code posted here cannot work. First I thought there were some typos, but now I know:

The code was damaged by transfering this thread from the old to the new Adobe Forum software last year.

 

You have to edit the code to fix it according to my post here:

https://community.adobe.com/t5/indesign/is-it-possible-to-write-a-script-in-indesign-to-do-condition...

 

Regards,
Uwe Laubender

( ACP )

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Feb 26, 2020 Feb 26, 2020

Copy link to clipboard

Copied

I try to edit the code, but I still got no error-message and the script doesn't work either.

for (var i = 0; i < tables.length; i++) {
var cells = tables[i].cells;

for (var c = 0; c < cells.length; c++) {

var cellValue = parseInt(cells[c].texts[0].contents);

if (cellValue >=5) {
cells[c].fillColor = svert_bra;
continue;
}

if (cellValue >= 4 && cellValue <=6) {
cells[c].fillColor = veldig_bra;
continue;
}

if (cellValue >= 3 && cellValue <=5) {
cells[c].fillColor = bra;
continue;
}

if (cellValue >= 2 && cellValue <= 4) {
cells[c].fillColor = mindre_bra;
continue;
}

if (cellValue >= 0 && cellValue <= 3) {
cells[c].fillColor = svakt;
}
}
}
}

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Aug 03, 2020 Aug 03, 2020

Copy link to clipboard

Copied

Hi there

Your support is amazing. Been trying to do all you mentioned before but keep getting error 21 "undefined is not an object" can you point to the right direction?

app.doScript(main, ScriptLanguage.JAVASCRIPT, undefined, UndoModes.ENTIRE_SCRIPT, "Conditional cell formatting");
function main() {
    sel = app.selection[0];
    if (sel.constructor.name != "TextFrame") {
        alert("Please select the textframe that contains the table.");
        exit();
    }
    // create color swatches
    var red = swatchCreator("Red", [15,100,100,0]);
    var yellow = swatchCreator("Amber", [0,51,99,0]);
    var green = swatchCreator("Green", [69,8,87,0]);
    tables = sel.tables;
    for (var i = 0; i < tables.length; i++) {
        var cells = tables[i].cells;     
        for (var c = 0; c < cells.length; c++) {          
            var cellValue = parseInt(cells[c].texts[0].contents);       
            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;

}

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Aug 03, 2020 Aug 03, 2020

Copy link to clipboard

Copied

What line are you getting the error on? It should say in the error message, and tell you the line of code that has the undefined value. 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Aug 03, 2020 Aug 03, 2020

Copy link to clipboard

Copied

It sounds silly but i would like to ask you one question, have you selected table while running script. If so please share screenshot with exact issue. Will help you out in resolving issue. I beleive that without selecting table you are running script.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Oct 28, 2020 Oct 28, 2020

Copy link to clipboard

Copied

Hi everyone...

Trying to adapt this script so it has 15 or so variables, however it's colouring the whole table one colour 😕

Can you see where it's going wrong?

app.doScript(main, ScriptLanguage.JAVASCRIPT, undefined, UndoModes.ENTIRE_SCRIPT, "Conditional cell formatting");
function main() {
    sel = app.selection[0];
    if (sel.constructor.name != "TextFrame") {
        alert("Please select the textframe that contains the table.");
        exit();
    }
    // create color swatches
    var red = swatchCreator("Red", [15,100,100,0]);
    var yellow = swatchCreator("Amber", [0,51,99,0]);
    var green = swatchCreator("Green", [69,8,87,0]);
    tables = sel.tables;
    for (var i = 0; i < tables.length; i++) {
        var cells = tables[i].cells;     
        for (var c = 0; c < cells.length; c++) {          
            var cellValue = parseInt(cells[c].texts[0].contents);    
    // Set colour per digit range - original code
            if (cellValue > 80) {
                cells[c].fillColor = red;
                continue;
            }
            if (cellValue = 81) {
                cells[c].fillColor = yellow;
                continue;
            }
            if (cellValue = 95) {
                cells[c].fillColor = green;
                continue;
                
 // Set colour per digit range - added code
            }
             if (cellValue = 1) {
                cells[c].fillColor = yellow;
                continue;              
                                }
             if (cellValue = 2) {
                cells[c].fillColor = yellow;
               continue;              
                                }
             if (cellValue = 3) {
                cells[c].fillColor = red;
                 continue;              
                                }
             if (cellValue = 4) {
                cells[c].fillColor = green;
                 continue;              
                                }
             if (cellValue = 5) {
                cells[c].fillColor = green;
                 continue;              
                                }
             if (cellValue = 6) {
                cells[c].fillColor = yellow;
                continue;              
                                }
             if (cellValue = 7) {
                cells[c].fillColor = red;
               continue;              
                                }
             if (cellValue = 8) {
                cells[c].fillColor = green;
                 continue;              
                                }
             if (cellValue = 9) {
                cells[c].fillColor = yellow;
                 continue;              
                                }
             if (cellValue = 10) {
                cells[c].fillColor = green;
                  continue;              
                                }
             if (cellValue = 11) {
                cells[c].fillColor = red;
                   continue;              
                                }
             if (cellValue = 12) {
                cells[c].fillColor = green;
               continue;              
                                }
             if (cellValue = 13) {
                cells[c].fillColor = yellow;
               continue;              
                                }
             if (cellValue = 14) {
                cells[c].fillColor = green;
                 continue;              
                                }
             if (cellValue = 15) {
                cells[c].fillColor = red;
            }
            }
            }
            }
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;

}

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 28, 2020 Oct 28, 2020

Copy link to clipboard

Copied

Boolean comparisons need to use two equal signs to get information about match, ie if (cellValue == 1). Using one equal sign changes the variable. Also, your first comparison uses a greater than comparison (cellValue > 80), which is skipping over the following two comparisons (81 and 82), even if they had the right syntax. 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Oct 28, 2020 Oct 28, 2020

Copy link to clipboard

Copied

Thanks for the reply.

I added the extra = sign... which in itself made no difference :/, but i have examined the code further.. Whilst it doesnt give me different colours as such, it's now performing sufficiently for my needs...

fresh code below 🙂

app.doScript(main, ScriptLanguage.JAVASCRIPT, undefined, UndoModes.ENTIRE_SCRIPT, "Conditional cell formatting");
function main() {
    sel = app.selection[0];
    if (sel.constructor.name != "TextFrame") {
        alert("Please select the textframe that contains the table.");
        exit();
    }
    // create color swatches
    var red = swatchCreator("Red", [15,100,100,0]);
    var yellow = swatchCreator("Amber", [0,51,99,0]);
    var green = swatchCreator("Green", [69,8,87,0]);
    tables = sel.tables;
    for (var i = 0; i < tables.length; i++) {
        var cells = tables[i].cells;     
        for (var c = 0; c < cells.length; c++) {          
            var cellValue = parseInt(cells[c].texts[0].contents);       
            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;
                continue;
                
 // Set colour per digit range - added code
            }
             if (cellValue == 1) {
                cells[c].fillColor = yellow;
                continue;              
                                }
             if (cellValue == 2) {
                cells[c].fillColor = yellow;
               continue;              
                                }
             if (cellValue == 3) {
                cells[c].fillColor = red;
                 continue;              
                                }
             if (cellValue == 4) {
                cells[c].fillColor = green;
                 continue;              
                                }
             if (cellValue == 5) {
                cells[c].fillColor = green;
                 continue;              
                                }
             if (cellValue == 6) {
                cells[c].fillColor = yellow;
                continue;              
                                }
             if (cellValue == 7) {
                cells[c].fillColor = red;
               continue;              
                                }
             if (cellValue == 8) {
                cells[c].fillColor = green;
                 continue;              
                                }
             if (cellValue == 9) {
                cells[c].fillColor = yellow;
                 continue;              
                                }
             if (cellValue == 10) {
                cells[c].fillColor = green;
                  continue;              
                                }
             if (cellValue == 11) {
                cells[c].fillColor = red;
                   continue;              
                                }
             if (cellValue == 12) {
                cells[c].fillColor = green;
               continue;              
                                }
             if (cellValue == 13) {
                cells[c].fillColor = yellow;
               continue;              
                                }
             if (cellValue == 14) {
                cells[c].fillColor = green;
                 continue;              
                                }
             if (cellValue == 15) {
                cells[c].fillColor = red;
            }
            }
            }
            }
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;

}

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 28, 2020 Oct 28, 2020

Copy link to clipboard

Copied

What color are you expecting for cellValue == 1, red or yellow? Because right now, it is going to red. If you want to handle the 1-15 range as you have it outlined, take out the continue statement from your first cellValue < 80 condition.  

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jun 21, 2021 Jun 21, 2021

Copy link to clipboard

Copied

LATEST

Hi all

What element should I add if I want all colored cells turn red to also change font color to white for legibility?

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;

}

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines