Google Doc Spreedsheet Conditional Formatting Script

Question

I know that Google Docs Spreadsheets does not currently support conditional formatting when comparing two cells.

if B3 >= E3, then E3 background is Green, but if B3 < E3, then E3 background is Red

Instead there is a work around with JavaScript'ing the solution.

I am new to scripting and can not find the correct language for but.

I found this, that would execute the first part of the formatting.

var value1Rule1 = s.getRange('b3').getValue();
var value2Rule1 = s.getRange('e3').getValue();
var range3Rule1 = s.getRange('e3');
var color1 = 'green';

if (value1Rule1 >= value2Rule1) range3Rule1.setBackgroundColor(color1);
else range3Rule1.setBackgroundColor('white');

But I don't know where I would put in the script that would turn it red if value1 is < value2.

Answer 1

Here's a good example you can use. https://webapps.stackexchange.com/questions/45937/conditional-formatting-using-other-cells-in-google-docs

Sounds like you need to wrap your code in a function name, add a new trigger with 'Run' set to and Events set to 'From spreadsheet' 'On edit.'

EDIT:

Here's another example that avoids using triggers: https://webapps.stackexchange.com/questions/16745/google-spreadsheets-conditional-formatting-based-on-another-cells-content?rq=1

function colorAll() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;
  var endRow = sheet.getLastRow();

  for (var r = startRow; r <= endRow; r++) {
    colorRow(r);
  }
}

function colorRow(r){
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getRange(r, 1, 1, 3);

  var data = dataRange.getValues();
  var row = data[0];

  if(row[0] === ""){
    dataRange.setBackgroundRGB(255, 255, 255);
  }else if(row[0] > 0){
    dataRange.setBackgroundRGB(192, 255, 192);
  }else{
    dataRange.setBackgroundRGB(255, 192, 192);
  }

  SpreadsheetApp.flush(); 
}

function onEdit(event)
{
  var r = event.source.getActiveRange().getRowIndex();
  if (r >= 2) {
    colorRow(r);
  }
}

function onOpen(){
  colorAll();
}
​

Answer 2

Currently a script is not required:

Select E3 to the end of the range to be formatted (eg E3:E99) and Format, Conditional formatting..., Format cells if... greater than and

=B3

with red fill. Add another rule (same range) with Less than or equal to and

=B3 

with green fill and Done.

Answer 3

Try this

var value1Rule1 = s.getRange('b3').getValue();
var value2Rule1 = s.getRange('e3').getValue();
var range3Rule1 = s.getRange('e3');
var color1 = 'green';

if (value1Rule1 >= value2Rule1) range3Rule1.setBackgroundColor(color1);
else range3Rule1.setBackgroundColor('red');

It turns cell to "red" in "else" statement.