Color cell RED if cell above value is lower

Question

I need a formula/script for a Google spreadsheet that will do this:

If the current cell value is higher than the value in the cell above the make the current cell background red (if less than or equal to then leave white), something like this: =IF((C34>B34),"make background red","leave background white") just not sure if this will work or I need a more complex script.

I need this formula to work across 224 cells (28 columns and 8 rows). Conditional formatting wont work.

There will be upto 20 people viewing the document on the day, only one will be editing the data. Will a script slow down the working of the live spreadsheet as I have a few more quite complex formulas I need to calculate the data from the main sheet to a summary sheet.

I have searched here and other forums but everyone's formulas are unique!

Cheers

Default

Answer 1

Conditional formatting wont work.

Probably not at the time that was written but it does now.

Assuming your array starts in B34 for its top left, clear formatting and select B35:AC41 and Format, Conditional formatting..., Format cells if... Custom formula is and:

=A35>A34

select red fill and Done.

Nabalas

Answer 2

You can use an onEdit() trigger to react to changes by reading the value from the cell above the one-just-changed, making the comparison, and coloring appropriately. You must do this in a script, you cannot control color from custom functions.

You said "If the current cell value is higher than the value in the cell above...", but your example then had =IF((C34>B34)..., which is "beside", not "above". This code uses .offset(-1,0) for "above", and ensures it won't mess with the row above row 1 - if you meant "beside", you'll want to change that.

function onEdit(event)
{
  if (isNaN(event.value)) return;        // If change was not a number, exit
  var changedCell = event.range;
  if (changedCell.getRow() == 1) return; // Nothing to do in Row 1
  var cellAbove = changedCell.offset(-1, 0);
  var background = 'white';              // Assume white background
  // Is the changed value greater than the value in the cell above?
  if ( parseFloat(event.value) > parseFloat(cellAbove.getValue()) ) {
    background = 'red';                  // Yes, so red background
  }
  changedCell.setBackground(background);
}

WRT performance, the trigger function will slow things a bit, but I suspect you'll see a greater lag due to the large number of multiple viewers, as google-docs does it thing to keep all those disparate views in sync. (I've seen sheets with < 100 cells and no formulas struggle to keep up with < 10 viewers.)

Analeptic