Google Sheets – Conditional Formatting Based on Previous Cell Content

conditional formattinggoogle sheets

Is it possible to conditionally format the background colour of a cell, based on the value in the column preceding it?

For example, in the following table (first row/column are titles):

  | A | B | C
1 | 1 | 5 | 2
2 | 2 | 7 | 0
3 | 3 | 1 | 2
4 | 1 | 3 | 4
5 | 1 | 5 | 0
6 | 4 | 1 | 2
7 | 8 | 2 | 4

B1 should be green, as it is greater than A1.

but

C5 should be red, as it is less than B1, etc

I'd like this formatting rule to work with any new column I add. The dataset I'm working with is date driven analytics.

Try as I might, I can either only seem to format the cells by:

A fixed number
A fixed cell

Best Answer

You can conditionally format based on another cell or column by selecting your cells, and making a conditional formatting rule for cells "Greater Than" the cell "A1" as shown below.

Sheets uses this as a relative reference that you're comparing the cell to the left of each cell in your selection.

Related Solutions

Google-sheets – How to set conditional formatting on particular cells that depend on another cell’s value in Google Spreadsheets

Where x = 1, in New Google Sheets in Custom formula is please try:

=or($D1="To do",$D1="Done")

check Background:, choose black and for Range: enter F1:N1

add another rule with Custom formula is

 =$D1="closed"

check Background:, choose green and for range: enter A1:N1.

Google-sheets – Conditional formatting using other cells in Google Docs

In your spreadsheet, go to tools, click script editor, and create a blank script. Replace any text inside the script with this function.

function paintRed() {

  // Gets the entire workbook
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // Gets the sheet you are concerned with
  // Change Sheet1 to the name of your sheet
  var sheet = ss.getSheetByName("Sheet1");

  // First 6 cells of the first row
  // Change to whatever you like
  var rangeToPaint = sheet.getRange("A1:F1"); 

  // Gets the number of the last row in the sheet 
  // that has anything in it
  var lastRow = ss.getLastRow();

  // If the sheet is blank, set the first 6 cells
  // in the first row to white and end the function
  if (lastRow < 1)
  {
    rangeToPaint.setBackground("white");
    return;
  }

  // Gets column A and column C
  var rangeColumnOne = sheet.getRange("A1:A"+lastRow.toString());
  var rangeColumnTwo = sheet.getRange("C1:C"+lastRow.toString());

  // Gets the length of column A and C
  var column1Length = rangeColumnOne.getValues().toString().replace(/,/g,"").length;
  var column2Length = rangeColumnTwo.getValues().toString().replace(/,/g,"").length;

  // If column A is blank or column C is not blank,
  // set the first 6 cells to white and stop the
  // function
  if ( column1Length < 1 || column2Length > 1 )
  {
    rangeToPaint.setBackground("white");
    return;
  }

  // Otherwise, paint the first 6 cells red
  rangeToPaint.setBackground("red");

}

You will need to change Sheet1 to whatever the name of your sheet is. Then, while still in the script editor, click resources, 'current project triggers.'

Add a new trigger with 'Run' set to paintRed and Events set to 'From spreadsheet' 'On edit.'

You should be good to go.

Link to spreadsheet you can copy to your drive, then play with - https://docs.google.com/spreadsheet/ccc?key=0Ar6b2EOADTG7dGJOM0lEalhjd3pNOHNRNnBDOV8xRnc&usp=sharing

Best Answer

Related Solutions

Google-sheets – How to set conditional formatting on particular cells that depend on another cell’s value in Google Spreadsheets

Google-sheets – Conditional formatting using other cells in Google Docs

Related Topic