Applying Conditional Formatting Color Scale to Non-Numerical Cells in Google Sheets

conditional formattinggoogle sheets

My column A is numerical, and I can apply conditional formatting color scales. However, I also want column B to be coloured according to the scale. The scale formatting does not accept formulas and will ignore non-numerical cells in the range, so I'm not sure how to do this.

Best Answer

As pnuts suggested, an Apps Script is currently the only way to do this. Here is how one can paint column B the same colors as column A: 

var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange("B2:B").setBackgrounds(sheet.getRange("A2:A").getBackgrounds());

However, you probably want these colors to remain in sync if changes are made to column A. This calls for an onEdit trigger, in which the function checks that the edit was made to column A of a certain sheet, and repaints if necessary.

function onEdit(e) {
  if (e.range.getColumn() == 1 && e.range.getSheet().getSheetName() == "Sheet1") {
    var sheet = SpreadsheetApp.getActiveSheet();
    sheet.getRange("B2:B").setBackgrounds(sheet.getRange("A2:A").getBackgrounds());
  }
}

This script tracks column A (== 1 is its number) of the sheet named "Sheet1".

Related Topic