Change cell background color onEdit based on value

Question

How can I change the cell background color based on the cell's content in an onEdit() function?

I've had many versions of code that I tested for this - some working almost right, some not working at all. But I have yet to get this to work the way I need it to.

Please forgive the lack of elegance in the way that this is written, but I actually need to keep the code as straightforward as possible since there will be many cell changes, many conditionals, and many differing numbers of cells that will be changed depending on what gets changed on the worksheet.

Ok, so here goes...

function onEdit(event)
{
 var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");   
 var changedCell= event.source.getActiveRange().getA1Notation();

 if (changedCell == 'B3') {
 var c = ss.getRange("B3").getValue();
  if (c < 2); {
  ss.getRange("B3").setBackgroundColor('#ff0000');  
  ss.getRange("B12").setBackgroundColor('#ff0000'); 
  }
  if (c > 1); {
  ss.getRange("B3").setBackgroundColor('#000000');  
  ss.getRange("B12").setBackgroundColor('#000000'); 
  }
 }
}

Answer 1

A few things to note

1.The name of the method is setBackground and not setBackgroundColor

2.How is the cell B3 formatted. The comparison works only if it is formatted as an integer. In most cases, Google Spreadsheet automatically formats the cells based on the data type, but if I'm writing code, I'd double check. So use something like

 var c = parseInt(ss.getRange("B3").getValue()) ;

3.The semicolon is not needed after the if condition. That will terminate the if condition immediately. So use

if (c < 2) {

and

if (c > 1) {

4.Finally, I don't know how data comes into B3, but if you have 1.5 in B3, then both the if conditions become true and your background color is overwritten. So, I suggest that you use a if..elseif

For better readability, I'd use setBackground('red') and setBackground('white') for the common colours.