Script to set cell background colour based on another cells value and text colour

Question

I'm looking to create a script where I can specify a cell value and text colour and then trigger this and it will colour all of the matching cells (value and text colour) with a background colour of Green.

For example, I have a cell with a value of '1' and the text colour is #4285f4, I want to get the value and text colour of that cell and then match it to a given range (A2:P60) and colour the background of any cells that match the value of '1' and text colour #4285f4 to green.

I have seen there is a textstyle class but I'm not sure how to bring all this together.

Any help is appreciated!

Answer 1

You can use this sample script as reference:

function checkCells() {
  var ss = SpreadsheetApp.getActive().getActiveSheet();
  var refValue = ss.getRange("A1").getValue(); //Used A1 as the cell with reference value and text color value
  var refTextColor = ss.getRange("A1").getTextStyle().getForegroundColor();
  var range = "A2:D15"; //Change range if you have a different one
  var columns = ss.getRange(range).getNumColumns();
  var lastrow = ss.getRange(range).getNumRows();

  for(var col=1; col<=columns; col++ ){
    for(var row=2; row<=lastrow; row++){ //Started the loop on row2 because row 1 has the cell reference values
      if(ss.getRange(row,col).getValue() == refValue && ss.getRange(row,col).getTextStyle().getForegroundColor() == refTextColor){
        Logger.log("Value of \""+ss.getRange(row,col).getValue()+"\" from Column #"+col+" & Row #"+row+" matched reference value of \""+ refValue+"\"");
        ss.getRange(row,col).setBackground("Green");
      }
    }
  }
}

Result

Sample Sheet:

After running the script: