google-sheetsgs-conditional-formatting

Highlight Row if Value in Column is above 0 - Google Spreadsheet


I am trying to get a Google spreadsheet to automatically highlight all rows in a spreadsheet where the value in a particular column is above 0. I have looked for a few solutions but haven't got it to work.

I have got various metrics in the columns, so say I want to highlight all rows, in which column "I" has a value of more than zero.

Can someone help me with this?


Solution

  • The only solution I'm aware of would be to write a script.

    Link: Google Apps Scripts

    The following is not pretty, but it works:

    function myFunction() {
      var I_INDEX = 1;
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Highlight rows");
      var dataRange = sheet.getDataRange();
      var dataValues = dataRange.getValues();
      for (var i=1; i<=dataRange.getNumRows(); i++) {
        var row = sheet.getRange(i, 1, 1, 2);
        if (dataValues[i-1][I_INDEX] > 0) {
          row.setBackground("red");
        }
      }
    }
    

    See example, use "Tools" --> "Script Editor..." to view/run the script.