Coloring selective cells

[#1]

Hi there, how do I color the cells red for values that are lower than the limits? Thanks.

dt = New Table( "Marks",

    Add Rows( 2 ),

    New Column( "Name", Character, Nominal, Set Values( {"Peter", "Leslie"} ) ),

    New Column( "Limits",

        Numeric,

        Continuous,

        Format( "Best", 12 ),

        Set Values( [20, 30] )

    ),

    New Column( "Column 1",

        Numeric,

        Continuous,

        Format( "Best", 12 ),

        Set Values( [10, 15] )

    ),

    New Column( "Column 2",

        Numeric,

        Continuous,

        Format( "Best", 12 ),

        Set Values( [30, 40] )

    ),

    New Column( "Column 3",

        Numeric,

        Continuous,

        Format( "Best", 12 ),

        Set Values( [20, 55] )

    ),

    New Column( "Column 4",

        Numeric,

        Continuous,

        Format( "Best", 12 ),

        Set Values( [53, 24] )

    ),

    Set Row States( [0, 1] )

)

fail = dt << Get Rows Where( :Column 1 < :Limits );

:Column 1 << color cells(Red, fail);

[#2]

The syntax looks right and you're code works well for me in JMP 12 (if inserting a semicolon between New Table(...) and fail ).

[#3]

Thanks MS, but the syntax works only on column 1. Are there any ways to make it work for all the columns? My example has only 4 columns but in practice, I'm actually having 200+ so I'm trying to figure out if there are any ways to do that.

[#4 · Accepted solution]

If you change your last 2 lines to:

for(i=2,i<=N Cols(dt),i++,

       fail = dt << Get Rows Where( as column(column(dt,i)) < :Limits );

column(dt,i) << color cells(Red, fail);

);

your columns will be changed

[#5]

Thanks Jim! That seems to work fine!

[#6]

HI Jim,
is it possible to apply color gradient to a column for selected rows only?

[#7]

First add a Value Colors column property, then use the Color Cell by Value( 1 ) message to apply the colors. You can use a gradient as the color theme for the column property.

See Help > Scripting Index, then select Data Table object > Column Scripting protocol and finally select Color Cell by Value message.

[#8]

Actually, I am not sure about the 'selected rows only' requirement. You might have to compute the associated color value and apply it to specific rows without the value color property.

[#9]

The color gradient does not honor excluded rows, so the best method that I found(a bit of a kluge) is below.  I colors all of the cells, and then comes back and changes to cell color for those colors you want to be changed back.  But it does it by placing a cell color on top of the gradient color.

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA\Big Class.jmp" );
dt:height << Set Property( "Color Gradient", {"Blue to Gray to Red"} );
dt:height << Color Cell by Value;
colorlist = As List( dt << get rows where( :sex == "F" ) );
dt:height << color cells( "White", colorlist );

[#10]

Hi,
I am also trying to color cells with a slight variation.
I have a row in .csv which is colored yellow.But after importing the color vanishes.

So trying to color that row in jsl using following code.But doesnt seem to work -
dinout << Select Rows( [40] );
<<color cells(Yellow);

Any inputs ?

thanks