conditional formatting rules

h_lazar

I have searched everywhere including the threads like this: Is there a way to send a font color to a single value in a display? So if X > 10 color X purple (given I know how to de…

But I can not find out the exact word for adding new conditional formatting rules for greater than or equal or just equal. Can someone list every combination? Here is an example of what I am doing. I need to use substitution to get the variables to work.

Eval(Substitute(Expr(Preferences(
     Conditional Formatting Rules( RuleSet(  RuleName( aaa ),
     GreaterThan( value(nnn), Format( Text Color( "White" ), FontStyle( Bold ), Back Color( 17 ) ) ), )))),
     Expr( aaa ), mystring,
     Expr( nnn ), myparam
     )
  );

The key is GreaterThan. I know that works and so does LessThan and capitalization matters!! I just don't know what to do for Equal, Not Equal, Greater than or Equal, and Less than or Equal. I have tried various combinations of capitalization and spacing but nothing has worked. I keep getting Unrecognized Rule.

Finally, does anyone know the code to delete a ruleset?

None of this information is anywhere to be found.

thanks in advance

H

DMR

Hi Heather,

This is a bit late for a reply to your original query, but I found myself in a very similar situation to the one you describe a couple of days ago - and this is what I did to get the effect I wanted:

First, use the interactive dialog as described in https://community.jmp.com/t5/Discussions/Is-there-a-way-to-send-a-font-color-to-a-single-value-in-a/td-p/8643 to create the set of conditions you want. What I wanted to do was to shade the cells of all the NumberColBoxes in a TableBox with a background color of red if the contents was less than 0.25, amber if it was greater than or equal to 0.25 but less than 0.75, and green if it was greater than or equal to 0.75.

 

I wanted the text color changed from black to white when the background color was red or green to make it easier to read, and I could also have also applied bold and/or italic options to the text if I'd wanted to. The dialog screen when looking at the "amber" condition looked like this:

　

 

Having set it up, I then needed to see what the above looked like in JSL so I could incorporate it into my script. To do that, type "show preferences()" into a script window and run it. The output shows all your preferences, including the one that's just been set up, which in my case looked like this:

 

RuleSet(

RuleName( "DMR_Test_01" ),

GreaterThan(

Value( 0.75 ),

Inclusive( 0 ),

Format( Text Color( "White" ), Back Color( {0, 200, 0} ) )

),

Range(

MinValue( 0.25 ),

MaxValue( 0.75 ),

InclusiveStart( 0 ),

InclusiveEnd( 0 ),

Format( Text Color( "Black" ), Back Color( "Medium Light Yellow" ) )

),

LessThan(

Value( 0.25 ),

Inclusive( 0 ),

Format( Text Color( "White" ), Back Color( "Red" ) )

)

)

)

 

I therefore incorporated the above rule set into a Preferences() statement of my own, then tested it on a TableBox with an assortment of columns of random numbers between 0 and 1 as shown. To give myself the option of building it into some function that I could run later on any TableBox whose structure might also include some StringColBoxes or PlotColBoxes, I tested each box to see if it was a NumberColBox before attempting to apply the conditional format to it. The final script looks like this:

　

Preferences(

Conditional Formatting Rules(

RuleSet(

RuleName( "DMR_Test_01" ),

GreaterThan(

Value( 0.75 ),

Inclusive( 0 ),

Format( Text Color( "White" ), Back Color( {0, 200, 0} ) )

),

Range(

MinValue( 0.25 ),

MaxValue( 0.75 ),

InclusiveStart( 0 ),

InclusiveEnd( 0 ),

Format( Text Color( "Black" ), Back Color( "Medium Light Yellow" ) )

),

LessThan(

Value( 0.25 ),

Inclusive( 0 ),

Format( Text Color( "White" ), Back Color( "Red" ) )

)

)

)

);

　

fn_RanUni = function({N}, {default local},

RanUniList = {}; for(i=1, i<=N, i++, insert into(RanUniList, Random Uniform()));

RanUniList

);

 

My_TableBox = TableBox(

StringColBox("SCB", {"AAA", "BBB", "CCC", "DDD", "EEE", "FFF", "GGG", "HHH", "III", "JJJ"}),

NumberColBox("NCB1", fn_RanUni(10), << set format("Fixed Dec", 10, 3)),

NumberColBox("NCB2", fn_RanUni(10), << set format("Fixed Dec", 10, 3)),

PlotColBox("PCB1", fn_RanUni(10)),

NumberColBox("NCB3", fn_RanUni(10), << set format("Fixed Dec", 10, 3)),

NumberColBox("NCB4", fn_RanUni(10), << set format("Fixed Dec", 10, 3)),

PlotColBox("PCB2", fn_RanUni(10)),

NumberColBox("NCB5", fn_RanUni(10), << set format("Fixed Dec", 10, 3)),

<< set shade alternate rows(0),

<< set shade headings(0),

<< set column borders(1),

<< set row borders(0),

<< set underline headings(1),

<< background color("white")

);

　

for(i=1, i<=nItems(My_TableBox), i++,

if((My_TableBox[i] << class name())=="NumberColBox",

My_TableBox[i] << set conditional format("DMR_Test_01")

)

);

 

new window("NW",

My_TableBox

);

 

I can now see from the Preferences how to get the correct syntax for (a) specifying a range, (b) changing the background color of a cell and (c) incorporating a "greater than or equal to" condition.  Running the above script should produce something like this:

 

 

I hope this might be of help to anyone trying to do something similar.