Changing Color of cell in google sheets

I would really appreciate if someone could help me.

I want to change cell color based on cell value. I can do this with conditional formatting but the orginal file is to big to do it and I am afraid that too many conditional formatting conditions will slow down the spreadsheet…

I created sample spreadsheet to show what I want to achieve.
Sample SpreadSheet
ConditionalFormating sheet

There is a list of products and theirs inventory status on the left. Above you have order list and which product it will need. I want to change color of the cell to any color except white to be able to see that I am out of stock for this order.

For example:
=SUM(D5:F5)>B5 => change color to red

I started to write a code but the I realized that with my approach I would have to write it for every row and then I got stuck…

    function myFunction() {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ConditionalFormating");
  var range1 = sheet1.getRange(10,7,1,sheet1.getLastColumn()).getValues();
  var inv1 = sheet1.getRange(10,3);

  for(var i = 0; i < range1.length; i++){
    if(range1[i] > inv1){
     sheet1.getRange(10,i).setBackground(`red`);
    }
  }

}

23 thoughts on “Changing Color of cell in google sheets”

Leave a Comment