gs-conditional-formatting

Script to Change Row Color when a cell changes text

//Sets the row color depending on the value in the “Status” column. function setRowColors() { var range = SpreadsheetApp.getActiveSheet().getDataRange(); var statusColumnOffset = getStatusColumnOffset(); for (var i = range.getRow(); i < range.getLastRow(); i++) { rowRange = range.offset(i, 0, 1); status = rowRange.offset(0, statusColumnOffset).getValue(); if (status == ‘Completed’) { rowRange.setBackgroundColor(“#99CC99”); } else if (status == ‘In Progress’) … Read more

Conditional Formatting from another sheet

For some reason (I confess I don’t really know why) a custom formula in conditional formatting does not directly support cross-sheet references. But cross-sheet references are supported INDIRECT-ly: =A1>INDIRECT(“SheetB!A1”) or if you want to compare A1:B10 on SheetA with A1:B10 on SheetB, then use: =A1>INDIRECT(“SheetB!A1:B10”) =A1>INDIRECT(“SheetB!”&CELL(“address”,A1)) applied to range A1:B10.