CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
ltz Butters
ltz Butters

Reputation: 31

How to change background color of row based on cell value? -Google Spreadsheet

I'm trying to figure out how to do the following on Google Spreadsheets: Put the letter 'x' in column A, row 2 (A column labeled "Status") and automatically change the background color of row 2 to RGB (244, 204, 204). I would want that for every row all the way down the spreadsheet.

Thanks for any help!

Upvotes: 3

Views: 10324

Answers (3)

Craig M.
Craig M.

Reputation: 11

@Dan Sudhakar's response works for the whole row as long as you adjust sheet.getRange('a'+n+':z'+n) where the :z to the last column in your sheet. I do need additional help on this however. How do I setup multiple ranges? I changed what would make sense to me but it works only for adjustments made to column C now and ignores A.

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  var rows = sheet.getRange('a1:a');
  var rows = sheet.getRange('c1:c'); // ADDED THIS //
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  for (var i = 0; i <= numRows - 1; i++) {
    var n = i+1;
    var bgColor = (values[i].indexOf('N/A') == -1) ? 'white' : 'red';
    sheet.getRange('a'+n+':b'+n).setBackgroundColor(bgColor);
   sheet.getRange('c'+n+':d'+n).setBackgroundColor(bgColor);  // ADDED THIS //
  }
};

Upvotes: -1

suDocker
suDocker

Reputation: 8534

Goto "Tools > Script Editor" and save and run the following code:

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  var rows = sheet.getRange('a1:z');
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  for (var i = 0; i <= numRows - 1; i++) {
    var n = i+1;
    var bgColor = (values[i].indexOf('N/A') == -1) ? 'white' : 'red';
    sheet.getRange('a'+n+':z'+n).setBackgroundColor(bgColor);
  }
};

It basically changes the background color of a row cells from 'A' to 'Z' when any of the cell contains the value 'N/A'.

Upvotes: 3

miturbe
miturbe

Reputation: 715

using onEdit() trigger you can use this code

function onEdit(e) {
    if (e.range.getColumn() ==1){
      e.range.setBackgroundRGB(244, 204, 204);
    }
}

Upvotes: 0

Related Questions