Google-sheets – Conditional formatting over wide area of cells, referencing other cells

conditional formattingformulasgoogle sheets

As data validation does not seem to be able to reference cells for numerical conditions (instead only being able to hardcode numbers); I have fallen back on conditional formatting. However, it does not seem to be possible to have more than any individual column affected by conditional formatting rule formulae

So while it is perfectly possible to do this:

Actually replicating this so that K3:K125 is compared with K1 does not seem to be possible. I would even settle for hardcoding all of the references like so:

However, this does not work.

Best Answer

Both data validation and conditional formatting can reference the values in other cells.

For example, if I require all values in column A to be greater than the value in cell C1, I would use Data Validation with custom formula =A1>C$1. The logic is as follows:

Validation rules (and conditional formatting rules) are entered as they should apply to the upper left corner of the range. From there the reference will be automatically mapped if needed: e.g., the rule =A1>C1 would compare A2 to C2, and so on.
The formula =X>Y returns True if X is greater than Y and False otherwise. Similarly, one can use expressions like =X>=Y or =X=Y. The initial = is just the symbol indicating we have a formula to evaluate.

With conditional formatting, you can use the same custom formula as above. Or, pick "greater than" from the list of criteria and enter =C$1 as the value to compare to. Not C$1, because C$1 is simply text "letter C, dollar sign, digit 1".

Related Solutions

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

Google Sheets – Protect Formatting While Allowing Data Changes

At this time Google Sheets doesn't have a way to protect cell format while allowing to edit its content.

References
How to share - Docs editors Help
Protect a range or sheet from being edited - Docs editors Help

Best Answer

Related Solutions

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

Google Sheets – Protect Formatting While Allowing Data Changes

Related Topic