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

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

• I don't understand how the DMC tab is relevant, if you give me an example of desired output and your non working solution I can make it work. Nov 17 '19 at 23:30
• Welcome. Your goal is unclear and there are many unexplained issues. 1) "Labels" and "Ref" have 130 values but "DMC no" has 456 values over 10 columns and 46 rows. 2) The relationship between values on "Ref", "Labels" and "DMC no" is not explained. 3) "Labels" links to Ref! Column B & C (which are empty) are not explained. Please read How do I ask a good question? and then edit your question to clarify the outcome you are trying to achieve. Also please provide an example of a successful outcome. Nov 20 '19 at 4:43
• May I try to clarify; please correct me. Use Label 11 as an example. There are three cells: A6, B6 and A7. A6 = `=Ref!A11` with value "11". B6 = `=Ref!B11` that returns an image. You want to enter a number in Cell A7. That number will also be a value on "DMC no"; each value on "DMC no" has a unique fill colour. When you enter the number in Cell A7, you want the fill colour to change to match the equivalent fill colour on "DMC no". e.g., you enter "333" in cell A7. On "DMC no", value "333" has a fill colour of hex #6E2E9B; you wish the fill colour of Cell A7 will change to hex#6E2E9B. Nov 21 '19 at 11:37
• Hi, yes that is correct. Sorry that I'm not the best at explaining this Nov 24 '19 at 18:07

## 1 Answer

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")
}

}
``````