Subscribe Bookmark RSS Feed

Coloring selective cells

kayne

Community Trekker

Joined:

Jan 20, 2016

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);

8 REPLIES
ms

Super User

Joined:

Jun 23, 2011

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

kayne

Community Trekker

Joined:

Jan 20, 2016

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.

txnelson

Super User

Joined:

Jun 22, 2012

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

Jim
kayne

Community Trekker

Joined:

Jan 20, 2016

Thanks Jim! That seems to work fine!

ram

Contributor

Joined:

Jun 5, 2017

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

Staff

Joined:

Jun 23, 2011

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.

Learn it once, use it forever!
markbailey

Staff

Joined:

Jun 23, 2011

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.

Learn it once, use it forever!
txnelson

Super User

Joined:

Jun 22, 2012

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 );
Jim