Script to color cells in a DoE setup

[#1]

When creating DoEs, each column is usually a mixture of 2 or 3 different values (high and low values, and sometimes a center value). To better illustrate the DoE setup to myself and my coworkers, I manually color the cells in each column. The high values are colored red, the center values are colored green, and the low values are colored blue. It would be beneficial to automate this coloring process. I want to be able to run a script that will color the cells in the selected columns according to the description above. Ideally, the script should be able to do this process for multiple selected columns simultaneously. Any suggestions as to how such a script could look like?

[#2]

How do you define low, high (and the sometimes middle)? Are there always only maximum of three values? After that decision is been made, it should be fairly simple to script using Value Colors column property with the methods mentioned hereInsert one expression into another using Eval Insert, Eval Expr, Parse, and Substitute based on the JMP provided code (with << get selected columns and loop)

Data Table("Untitled 10"):Column 1 << Set Property(
	"Value Colors",
	{1 = -15113984, 2 = -5682409}
);

[#3]

Thanks @jthi, 

In most cases, there are only two values: a maximum and a minimum value. If it is a central value, it is the arithmetic mean between the min and max values. The Value Colors column property is definitely the correct tool, even without scripting. When used on multiple columns simultaneously it certainly simplifies the process!

[#4 · Accepted solution]

Below is example for the script (requires JMP16 due to Transform Each() and For Each())

Names Default To Here(1);

If(Is Empty(Current Data Table()),
	Throw("No tables open.");
, // else
	dt = Current Data Table();
);

sel_cols = dt << Get Selected Columns();
If(N Items(sel_cols) == 0,
	Throw("No columns selected");
);

// color "globals", fairly free to choose which type of "JMP Color" to use
LOW_COLOR = 67;
MIDDLE_COLOR = -13948116;
HIGH_COLOR = "Light Green";

For Each({col_ref}, sel_cols,
	col_name = col_ref << get name; // I don't like using :COLNAME format at all
	
	// get values in column
	// Associative Array seems to have a bug so we use Summarize
	Summarize(dt, val_strs = By(Column(col_name)));
	// Summarize returns list of characters, so we will convert them to numbers
	vals = Transform Each({val}, val_strs, Num(val));
	min_val = Remove From(vals, 1)[1];
	max_val = Remove From(vals, -1)[1];
	
	// This method should be fairly robust, but it does looks complicated
	weird_jmp_list = {};
	Eval(Substitute(Expr(
			Insert Into(weird_jmp_list, Expr(_value_ = _color_))
		),
		Expr(_value_), min_val,
		Expr(_color_), LOW_COLOR
	));
	Eval(Substitute(Expr(
			Insert Into(weird_jmp_list, Expr(_value_ = _color_))
		),
		Expr(_value_), max_val,
		Expr(_color_), HIGH_COLOR
	));
	
	// color rest with middle color
	For Each({val}, vals,
		Eval(Substitute(Expr(
				Insert Into(weird_jmp_list, Expr(_value_ = _color_))
			),
			Expr(_value_), val,
			Expr(_color_), MIDDLE_COLOR
		));		
	);
	Column(dt, col_name) << set property( "Value Colors", weird_jmp_list) << Color Cell By Value(1);
);

[#5]

Thanks! 

A perfect solution that worked directly!

This should actually be a part of the DOE platform in JMP. Is it OK that I add it to the wish list @jthi?

[#6]

@Steffen_Bugge sure and I would say that it is recommendable to write new ideas to Wish List if they come to your mind