Conditional Formatting based on another sheet, tying a value to a colour, Google Sheets

Question

Does anyone know how I'd be able to make a specific value always appear with the same formatting & colour in my sheet, based on another tab in the sheet?

The spreadsheet I'm trying to do this on is this: https://docs.google.com/spreadsheets/d/1FDmyTa4hMcBJZGYKlh8LbvLJWwLs-FW13JJEvAkp0fQ/edit?usp=sharing

They're labels for my hobby, I've set it up so that the ref tab goes into each label in order but when I reference a number it won't bring the formatting from the tab named "DMC no." with it, I was hoping someone would be able to help me with this in some way, whether it means conditional formatting on the label sheet or being able to reference a cell and the format being bought with it. Conditional formatting on the label sheet would be preferable, but whatever you can do to help me would be good!

I changed and edited the google sheet to further explain what I want to achieve, with a better explanation at the top of the labels sheet. Thank you

Answer 1

You are trying to update the value AND the background colour of a cell when a certain "DMC" number is entered. DMC information is held on a sheet ("DMC no.") and consists of cells with given numerical values and specific background colours.

---

Revising the DMC data format

There is no function that enables Google Sheets to get the background colour of a given cell, so I propose that the DMC information needs to be recorded in a different format - a 2 Column list. The first column is the DMC value and the second column is the hex colour value associated with that DMC value.

This function modifies the existing DMC information. I created a new sheet "DMC_data" to hold the revised information.

---

function wa13636001() {

  // built a DMC value/colour matrix

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "DMC no.";
  var sheet = ss.getSheetByName(sheetname);

  // get DMC sheet info
  var dmcLR = sheet.getLastRow();
  var dmcLC = sheet.getLastColumn();

  // get the Range and Values  
  var dmcRange = sheet.getRange(1, 1, dmcLR, dmcLC);
  var dmcValues = dmcRange.getValues();

  //  create termporary array
  var dmctemparray=[];

  // loop rows and columns to build value list
  for (var i = 0; i<dmcLR;i++){
    for (k=0;k<dmcLC;k++){
      dmctemparray.push([dmcValues[i][k]])
    }
  }

  //  get length
  var rows = dmctemparray.length

  // setup target sheet
  var targetname = "DMC_data";
  var target = ss.getSheetByName(targetname);

  // get target range - Column 1 - values
  var targetRange = target.getRange(1, 1, rows);
  // update values
  targetRange.setValues(dmctemparray);

  // get the background colours
  var bgColors = dmcRange.getBackgrounds();

  // create a temporaryarray for the colours
  var dmccoloursarray = [];
  for (var i in bgColors) {
    for (var j in bgColors[i]) {
      dmccoloursarray.push([bgColors[i][j]]);
    }
  }

  // get target range - Column 2 - cdolours
  var targetRange = target.getRange(1, 2, rows);
  // update colour values
  targetRange.setValues(dmccoloursarray);

}

---

Custom Function - not an option

I wrote a custom function to update the background colour in the target cell (A7) and got an error:

"You do not have permission to call setBackground".

I then read Google Apps script - change the background color of a cell with a hex value from another cell and, as they point out, "Formulas cannot modify cell format". So it's not feasible to create a function that will do this in real time.

Two options seemed available:

• a script that can be called from a button which would update any/all DMC values.
• an onEdit(e) script. This is a far more sensible option in the circumstances

---

onEdit() - a logical option

onEdit(e) needs to take advantage of the Event objects.

The evaluation of edited data is in three parts:

1. sheetname === labelsname: ensure that the edit took place on sheet = "Labels".

2. modrow === 0 && modcol===0

   • DMC Values appear in predictable rows and columns.
   • Rows: the first row is #4, and then every third row thereafter. So the remainder value is predictable.
   • var modrow = (+row-dmcstartrow)%3;: this value should be zero; if not then the DMC value has not been edited.
   • Columns: the first column is 1, and then every third column thereafter. Again the remainder value is predictable.
   • var modcol = (+col-dmcstartcol)%3;: this value should be zero; if not, then the DMC value has not been edited.

3. the script then loops through the DMC data looking for a match on the edited value. If a match is found, then the hex background colour is obtained from the adjacent column, and the edited cell is updated

   • range.setBackground(data[nn][1]);

---

 function onEdit(e) {
  // wa13636003 

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var labelsname = "Labels";
  var labels = ss.getSheetByName(labelsname);
  //Logger.log(JSON.stringify(e)); //DEBUG

  // get event objects  
  var range = e.range;
  var row = range.getRow();
  var col = range.getColumn();
  var value = e.value
  var sheetname = range.getSheet().getSheetName();

// dmc sheet start row/col  
  var dmcstartrow = 4;
  var dmcstartcol = 1;
  // calculate mod on row and colum
  var modrow = (+row-dmcstartrow)%3;
  var modcol = (+col-dmcstartcol)%3;
  //Logger.log("DEBUG: modrow:"+modrow+", modcol:"+modcol)

  // test if mod for row and col are zero and that the sheet is Labels.
  if (modrow === 0 && modcol===0 && sheetname === labelsname){
    // Logger.log("DEBUG: this cell is a match")

    // get DMC data
    var dmc = ss.getSheetByName("DMC_data");
    var last=dmc.getLastRow();
    var data=dmc.getRange(1,1,last,2).getValues();// create an array of data from columns A and B

    //loop through dmc data to find the match on the value, and return the colour
    for(nn=0;nn<data.length;++nn){
      if (data[nn][0]==value){
        // Logger.log("DEBUG: "+data[nn][0]+" is a match for "+value);

        // set the background colour        
        range.setBackground(data[nn][1]);

        // break out rather than cvontinue going through the loop.
        break;
      } // if a match in column B is found, break the loop
      else{
        // Logger.log("DEBUG: "+data[nn][0]+" is NOT a match for "+value)
      }
    }
  }
  else{
    // Logger.log("DEBUG: this cell is NOT a match")
  }

}