Color Cells that are Higher or Lower than the NET

This code allows you to highlight cells for each row which are greater or lower than the value in the NET column for that row. The code checks the first statistic shown in the table. You can customize the colors and the level of difference required for highlighting by modifying the top 5 lines.

var high_color = "Green";
var low_color = "Red";

// How much higher or lower should cells be colored?
var difference_level = 5;


// Which column to compare with?
var total_column_label = "NET";
var total_column_index = table.columnIndex(total_column_label);

form.setSummary("Color cells larger or smaller than " + total_column_label + " by " + difference_level + " or more.");
if (total_column_index == null)
    form.ruleNotApplicable("there is no column called " + total_column_label + " in this table");

// Which statistic to use?
// Use the first one selected 
var statistic = table.statistics[0];
var values = table.get(statistic);
var colors = table.cellColors;

for (var row = 0; row < table.numberRows; row++) {
    var row_total = values[row][total_column_index];
    for (var col = 0; col < table.numberColumns; col++) {
        if (row_total - values[row][col] >= difference_level)
            colors[row][col] = high_color;
        else if (values[row][col] - row_total >= difference_level)
            colors[row][col] = low_color;
    }
}

table.cellColors = colors;

See also

• Table JavaScript and Plot JavaScript for an explanation of how to run this code.
• Table JavaScript and Plot JavaScript Reference for technical information.
• Table JavaScript and Plot JavaScript Examples Library for other examples.
• JavaScript for information about the JavaScript programming language.
• QScript for tools for automating projects using JavaScript.
• JavaScript Variables for detail on how to create new variables in the Variables and Questions tab using JavaScript.