3

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.

3

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".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.