turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Re: Color cells based on multiple conditions

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Dec 17, 2018 9:49 AM
(206 views)

I have written the following code to color the cells based on condition.I need to add one more condition -

if weight is within -2% of Target then need to color cells yellow.How do I do this?

```
dt << New Column("color cells",
Formula(If(:Target> :weight, :weight << color cells("Red", Row()); .,
:weight << color cells("Green", Row()); .)),
Hide(1)
);
```

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

If(

:Target > :weight,

:weight << color cells( "Red", Row() );

.;,

:weight < :Target + (.02 * Abs( :Target )) & :weight > :Target - (.02 *

Abs( :Target )),

:weight << color cells( "Yellow", Row() );

.;,

:weight << color cells( "Green", Row() );

.;

);

Jim

3 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

If(

:Target > :weight,

:weight << color cells( "Red", Row() );

.;,

:weight < :Target + (.02 * Abs( :Target )) & :weight > :Target - (.02 *

Abs( :Target )),

:weight << color cells( "Yellow", Row() );

.;,

:weight << color cells( "Green", Row() );

.;

);

Jim

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Need to script a variation of this logic. I have data in 4 columns and need to compare the value in each cell with the Target value. Also I wouldnt know the column names before hand. How to do this? In simple words,need to compare values in column A,B,C and D with corresponding value in Target column and color code(Green,Yellow or Red) accordingly.

Target | A | B | C | D |

99% | 1% | 8% | 93% | 10% |

52% | 55% | 62% | 22% | 19% |

39% | 89% | 18% | 33% | 25.50% |

16% | 11% | 92% | 10% | 2% |

Highlighted
##
##### Re: Color cells based on multiple conditions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

1. If you are going to be working in JSL, you need to take the time to read the Scripting Guide: Help==>Books==>Scripting Guide

2. Below is a simple script that generates the coloring as you described

```
Names Default To Here( 1 );
dt = Current Data Table();
colNamesList = dt << get column names( string );
For( theRow = 1, theRow <= N Rows( dt ), theRow++,
For( theCol = 2, theCol <= N Items( colNamesList ), theCol++,
If(
:Target[theRow] > Column( dt, colNamesList[theCol] )[theRow],
Column( dt, colNamesList[theCol] ) << color cells( "Red", theRow );
.;,
Column( dt, colNamesList[theCol] )[theRow] < :Target[theRow] + (.02 *
Abs( :Target[theRow] )) & Column( dt, colNamesList[theCol] )[theRow] >
:Target[theRow] - (.02 * Abs( :Target[theRow] )),
Column( dt, colNamesList[theCol] ) << color cells( "Yellow", theRow );
.;,
Column( dt, colNamesList[theCol] ) << color cells( "Green", theRow );
.;
)
)
);
```

Jim