Chaing the cell colour on a range of cells at once

I am trying to change the cell colour to a bunch of cells at once, they are currently saved in a range, but I don't want all the cells to change, only ones under a certain condition . I can change each cell individually but it seems slow to make that many calls to that function so I'm thinking that if I can construct a range of cells then I can use the setBackground() command to make things more efficient.

So I guess it's an open question about the best way to do this?

The direction I'm heading in is:

• We need to use a range
• Ranges can only have a "block" of cells in them and that might include cells I don't want to change colour of
• Perhaps there's a way to remove cells from a range? If so I can iterate over and remove the ones I don't want and then operate on the remainder
• Perhaps I can save the cells to an array and then construct a range from the array? But that gets blocked by the idea that, I think, ranges are a rectangular block with two corner cells and everything else inbetween
• Perhaps there's some other construct that holds many cells at once and can operate on them like a range can?

This is the loop I have already:

  for (var i = 1; i <= numRows; i++) {
    for (var j = 1; j <= numColumns; j++) {
      var mCell = monthRange.getCell(i, j);
      var mValue = mCell.getValue();

      if (mValue != "")
      {
        var eCell = exerciseRange.getCell(1, mValue);
        var eValue = eCell.getValue();
      }
      
      if (mValue != "" && eValue == true) {
        mCell.setBackground('green');
      } 
    }
  }

Any help is much appreciated as I'm rather new to this.

Cheers! TM

You should get all the ranges, values and backgrounds once, and store them as arrays. Then loop through the arrays and make the comparisons, storing the backgrounds in an array. And only setting once the array:

function myFunction() {
  var sheet = SpreadsheetApp.getActive()
  var monthRange = sheet.getRange("A1:C50000")
  var monthValues = monthRange.getValues() // get the values as a range

  var exerciseRange = sheet.getRange("D1:K1")
  var exerciseValues = exerciseRange.getValues() //the same with the exercise range

  var numColumns = monthRange.getNumColumns()
  var numRows = monthRange.getNumRows()
  var backgrounds = monthRange.getBackgrounds() // also get the backgrounds as a range

  for (var i = 0; i < numRows; i++) {
    for (var j = 0; j < numColumns; j++) {
      var mValue = monthValues[i][j] // use the array of values
      if (mValue != "") {
        var eValue = exerciseValues[0][mValue-1] //and the array of the exercise
        if (eValue == true) {
          backgrounds[i][j] = "green" // and store the background in its array
        }
      }
    }
  }

  monthRange.setBackgrounds(backgrounds); //Only once all the backgrounds are set

}

See this example, I've tested it with 150.000 cells(50.000 rows *3 columns) and it lasted 15 seconds!!