Multiple column conditional formatting

Question

I have searched for an answer to this, but I cannot find one anywhere.

I am trying to set a background color of an individual cell based on the contents of that cell and another cell.

For example, I want A1 to have a background color of green if that specific cell is blank AND if B1 does not have an "x" in it. If either condition is not met, i.e. there is something in A1 or B1 does not have an "x", I don't want A1 to be highlighted.

I then want to apply this to the entire column A, and have it update each time columns A or B are edited.

Bryan P · Accepted Answer · 2013-05-21 14:09:25Z

2

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(),
      s = ss.getSheetByName('Sheet1'),
      values = s.getRange('A1:B').getValues(),
      row, len, colors = [];

  // CHECK VALUES FOR CONDITIONS
  for (row = 0, len = values.length; row < len; row++) {
    if (values[row][0] == '' && values[row][1] != 'x')
      colors.push(['green']);
    else colors.push(['white']);}

  // SET NEW COLORS ALL AT ONCE
  s.getRange('A1').offset(0, 0, len).setBackgrounds(colors);
}

edited May 21, 2013 at 14:09

answered May 20, 2013 at 21:16

Bryan P

5,0613 gold badges32 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2403179 Over a year ago

Thank you BryanP. The only modification I made was in row 9, where I changed "if (values[row][0] == '' && values[row][1] != 'x')" to "if (values[row][0] == '' && values[row][1] == 'x') and then it worked perfectly. Thanks again!

Collectives™ on Stack Overflow

Multiple column conditional formatting

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related