Copying background color from cells on a different tab of the same sheet

Question

In Google Sheets I have two tabs with different information. Tab A has a table I am using as a summary of variables per person. Tab B has the color coding of each of the variables in a random order. What I am trying to achieve is to have the summary table on Tab A be linked to the colour coding on tab B. So whenever the color of each variable changes on Tab B it will change on Tab A as well.

Example

Is it possible to keep the typed value inside the cells of Tab A but copy the color coding (background color) in Tab B?

I think the only way would be with a custom function or script.

I tried with conditional formatting however the problem is that I can only get it to work if all the data is on the same tab.

Custom formulas in conditional formatting can refer to another sheet, you just need to wrap such cell references in indirect. So, instead of something like =Sheet2!A3>Sheet3!B4 you would have =indirect("Sheet2!A3") > indirect("Sheet3!B4"). — user6655984
Thanks for your reply. However I am still having trouble translating being able to reference that cell into copying the color of cells on Sheet A to Sheet B. — Thomas Gardner

Ed Nelson Ed Nelson · Accepted Answer · 2016-12-13T16:06:08

This should do what you want. It will also handle adding people and vehicles. I am also sharing the spreadsheet I tested it on.

function colorCode() {
  var ss=SpreadsheetApp.getActiveSpreadsheet()
  var lr=ss.getSheets()[0].getLastRow() // get last row of sheet1
  var lc=ss.getSheets()[0].getLastColumn() //get last column of sheet1
  var lr1=ss.getSheets()[1].getLastRow() // get last row of sheet2
  var lc1=ss.getSheets()[1].getLastColumn() ////get last column of sheet2
  var sv=ss.getSheets()[0].getRange(1,2,1,lc-1).getValues() // get vehicles
  var sn=ss.getSheets()[0].getRange(2,1,lr-1,1).getValues() // get names
  var s1=ss.getSheets()[1].getRange(2,1,lr,lc-1)//exclude lehend
  var rng1=s1.getValues() // get sheet2 data
  var rng2=s1.getBackgrounds() // get background colors of dheet2 data

  var col=1 //column for vehicles om sheet1
   for(var m=0;m<sv[0].length;m++){ //for each vehicle
      col=col+1 //add one to vehicle column
  for(var n=0;n<sn.length;n++){ //for each name
  for(var i=0;i<sn.length;i++){ //loop sheet2 data
  for(var j=0;j<rng1[0].length;j++){
    if(rng1[i][j].indexOf(sv[0][m])>-1 && rng1[i][j].indexOf(sn[n][0])>-1){ //if sheet2 data cell contains vehicle and name
       var c=ss.getSheets()[0].getRange(n+2, col).setBackground(rng2[i][j]) //set color of vehicle and name on sheet1
  }}}}}
}

Test spreadsheet:

https://docs.google.com/spreadsheets/d/13zudMf86oOXHMSYg6rJGPguNfRxCr0aUHAktr0RqWzY/edit?usp=sharing

To shift the table on sheet1 down 4 rows use this. The changes are noted in the code.

function colorCode() {
  var ss=SpreadsheetApp.getActiveSpreadsheet()
  var lr=ss.getSheets()[0].getLastRow() // get last row of sheet1
  var lc=ss.getSheets()[0].getLastColumn() //get last column of sheet1
  var lr1=ss.getSheets()[1].getLastRow() // get last row of sheet2
  var lc1=ss.getSheets()[1].getLastColumn() ////get last column of sheet2
  var sv=ss.getSheets()[0].getRange(5,2,1,lc-1).getValues() // get vehicles//CHANGED 1 tp 5
  var sn=ss.getSheets()[0].getRange(6,1,lr-5,1).getValues() // get names//CHANGED 2 to 6 and -1 to -5
  var s1=ss.getSheets()[1].getRange(2,1,lr,lc-1)//exclude legend
  var rng1=s1.getValues() // get sheet2 data
  var rng2=s1.getBackgrounds() // get background colors of sheet2 data

 var col=1 //column for vehicles on sheet1
   for(var m=0;m<sv[0].length;m++){ //for each vehicle
      col=col+1 //add one to vehicle column
  for(var n=0;n<sn.length;n++){ //for each name
  for(var i=0;i<sn.length;i++){ //loop sheet2 data
  for(var j=0;j<rng1[0].length;j++){
    if(rng1[i][j].indexOf(sv[0][m])>-1 && rng1[i][j].indexOf(sn[n][0])>-1){   //if sheet2 data cell contains vehicle and name
       var c=ss.getSheets()[0].getRange(n+6, col).setBackground(rng2[i][j]) //set color of vehicle and name on sheet1//CHANGED +2 to +6
  }}}}}
}

Copying background color from cells on a different tab of the same sheet

1 Answers