How To set the Background Color of Values From One Sheet To the Cells with matching Values in Anothe-CodePudding

Problem:

I'm trying to do the following:

Set the same background colors from cells in Sheet2 to the cells in Sheet1 that do have the same values as in Sheet2.

The idea is to compare all the values from Sheet2 that are also present in Sheet1, and take the premade backgrounds from cells in Sheet2 and set those premade backgrounds from Sheet2 to the cells with matching values in Sheet1.

The goal is automatically set the backgrounds based on the cells values when matching regardless of order (to avoid having to do it manually for hundreds of different matching values).

Illustration example:

In Sheet2, I have preset a range A1:A10 with 10 different values all with their unique background.

In Sheet1, I have the same 10 values as in Sheet2 and also other non matching values. Also, the 10 matching values in Sheet1 are 'scattered around' in range A1:AG20 (different range from Sheet2, could be any range).

The result I'm trying to get would be:

Other Close Questions:

I've found close questions here:

The result I'm trying to get with repeated values in Sheet1 would be:

The current Sheet1 result:

Under Format Rules, scroll to the bottom of the drop-down list to find "Custom Formula is"

In the text input box, copy and paste the code above

To change the colour, use the Formatting options

Please note: when changing cell, change the code A1 to the current cell.

CodePudding user response：

function copycolors() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  const cA = sh.getRange(1,1,sh.getLastRow()).getDisplayValues().flat();//values
  const bA = sh.getRange(1,1,sh.getLastRow()).getBackgrounds().flat();//colors
  let colors = {pA:[]};
  cA.forEach((c,i) => {
    colors[c]=bA[i];
    colors.pA.push(c);
  });
  const osh = ss.getSheetByName('Sheet1');
  const vs = osh.getDataRange().getDisplayValues();
  const bs = osh.getDataRange().getBackgrounds();
  let bgA = vs.map((r,i) =>{
    r.forEach((c,j) =>{
      let idx = colors.pA.indexOf(c);
      if(~idx) {
        bs[i][j] = colors[c];
      }
    });
    return bs[i];
  });
    
  osh.getRange(1,1,bgA.length,bgA[0].length).setBackgrounds(bgA);

}

~ bitwise not