Conditional formatting by comparing with other column / same row?

[chrisj-back2work]

I'd like to be able to express a rule like: if any cell in column E is greater than it's neighboring column F, make E red.

I don't want to create separate rules comparing E1 to F1, E2 to F2 etc. -- would like to declare a rule for the whole column.

I've tried things like range: "E:E", criteria: ""E>F" or criteria "E1>F1" but have not been able to implement this logic.

Is there a way for each cell to refer to its neighbor?

[GBonnaire]

Hi,

Yes it’s possible, i will send more detail tomorrow (i am on my phone). You can use in criteria property a function (not a string formula). I think this one it’s better solution (more speed).

With formula, you can also with formula relative like « =VALUE(COL()-1, ROW(), TRUE) » (more détail in documentation formula JSS Pro.

Howerver, i suggest use function, better and speedest.

Tomorrow i will send you here one example

[GBonnaire]

In waiting you have documentation here : https://github.com/GBonnaire/jspreadsheet-plugins-and-editors/wiki/Premium-plugin-:-Conditional-Style

[GBonnaire]

Hi,

With example:

jspreadsheet(document.getElementById('spreadsheet), {
    plugins:[
        { name:'conditionalstyle', plugin:jss_conditionalstyle, options:{rules:[
                     {criteria: "=IF(VALUE(COL()-1,ROW(),TRUE)==1, true, false)", style:{"background-color": "lightblue"}, stopIfTrue:true}, // Criteria with formula relation position
                     {range:"1:1", criteria: function(worksheet, cellEl, col, row, val) {return ((col+1) % 2==0 && val=="OK");}, class:"cellAlert"}, // Criteria with function
                     {range:"F:F", criteria: checkCell, style:{"color": "red", "font-weight":"bold", "background-color": "LightPink"}}, // Criteria with function name
                     {range:"E:E", criteria: true, style:"background-color:green"}, // Criteria with bool
             ]}
         }, 
     ],
});

function checkCell(worksheet, cellEl, col, row, val) {
    // get value relative cell
    var value = worksheet.getValueFromCoords(col-1, row, true); // get processed value from cell to left
    if(value == "OK") {
         return true;
    }
    return false;
}