Hello,
I'm trying to get a script for applying cell styles based on content. Since I'm really new to scripting I can't get this to work as I want to. I have a script that will find a number and change the cell style based on the number, but I need to use a second formula for finding the cell to apply the cell style to, based on the Column Title.
So for example I have four columns that are "ranks," and those would just have red, yellow, or green backgrounds applied (which I change with a cell style)(e.g. 0-49.99, 50.00-69.99, 70.00-100). But then I have four columns that are "grades," and they have more variety in the colors (red, orange, yellow, green, blue; e.g. 0-24.99, 25.0-44.99, 45.0-59.99, 60.0-79.99, 80.0-100).
The following script does what I want for the first situation, but it then I can't run the second script because it will change the colors I already changed using the first script. Is there a way to search by column title and apply the formula, so I don't have to go through and manually select each text frame to apply the second forumula? Does that make sense?
Can anyone help me with this?
var myDoc = app.activeDocument ;
var myFrame = app.selection[0];
if(app.selection <1){
alert ("Please select a table or a frame containing a table.");
}
app.findGrepPreferences = app.changeGrepPreferences = null;
app.findGrepPreferences.findWhat = "^[6-7]\\d+(\\.\\d+)?";
var myFound = myFrame.findGrep();
for( var i=0; i<myFound.length; i++)
{
if( myFound[i].parent.constructor.name == "Cell" )
{
myFound[i].parent.appliedCellStyle = "Grade Yellow";
var overrides = myFound[i].clearOverrides(); //this is the new line added in this content
};
};
app.findGrepPreferences = app.changeGrepPreferences = null;
app.findGrepPreferences.findWhat = "^[8]\\d+(\\.\\d+)?";
var myFound = myFrame.findGrep();
for( var i=0; i<myFound.length; i++)
{
if( myFound[i].parent.constructor.name == "Cell" )
{
myFound[i].parent.appliedCellStyle = "Grade Green";
var overrides = myFound[i].clearOverrides(); //this is the new line added in this content
};
};
app.findGrepPreferences = app.changeGrepPreferences = null;
app.findGrepPreferences.findWhat = "^[9]\\d+(\\.\\d+)?";
var myFound = myFrame.findGrep();
for( var i=0; i<myFound.length; i++)
{
if( myFound[i].parent.constructor.name == "Cell" )
{
myFound[i].parent.appliedCellStyle = "Grade Blue";
var overrides = myFound[i].clearOverrides(); //this is the new line added in this content
};
};
17
Replies
If you are glad to pay someone …
In this case! 😉
// Place the cursor in the cell of a table
var myCells = app.selection[0].parent.parent.cells.everyItem().getElements(),
C = myCells.length, c;
for ( c = 0; c < C; c++ ) {
if ( myCells[c].columns[0].cells[0].texts[0].contents.match(/^RANK/) && myCells[c].contents != "" ) {
if ( Number(myCells[c].contents) >= 0 && Number(myCells[c].contents) < 50 ) myCells[c].appliedCellStyle = "Rank Red";
if ( Number(myCells[c].contents) >= 50 && Number(myCells[c].contents) < 70 ) myCells[c].appliedCellStyle = "Rank Yellow";
if ( Number(myCells[c].contents) >= 70 && Number(myCells[c].contents) <= 100 ) myCells[c].appliedCellStyle = "Rank Green";
}
if ( myCells[c].columns[0].cells[0].texts[0].contents.match(/^GRADE/) && myCells[c].contents != "" ) {
if ( Number(myCells[c].contents) >= 0 && Number(myCells[c].contents) < 25 ) myCells[c].appliedCellStyle = "Grade Red";
if ( Number(myCells[c].contents) >= 25 && Number(myCells[c].contents) < 45 ) myCells[c].appliedCellStyle = "Grade Orange";
if ( Number(myCells[c].contents) >= 45 && Number(myCells[c].contents) <= 60 ) myCells[c].appliedCellStyle = "Grade Yellow";
if ( Number(myCells[c].contents) >= 60 && Number(myCells[c].contents) <= 80 ) myCells[c].appliedCellStyle = "Grade Green";
if ( Number(myCells[c].contents) >= 80 && Number(myCells[c].contents) <= 100 ) myCells[c].appliedCellStyle = "Grade Blue";
}
}
Awesome, thank you. I will definitely donate to the local hospital.
HOwever, I do get an error now with the script:
which I assume is obviously because I need to change line 8 to match something you changed in line 2, but so far I have not guessed or gleaned what.
AdChoices