Google Sheets Conditional Formatting changes when new rows added

Question

So I click on the cell between A and 1 to select entire sheet, then I click "Format" then "Conditional Formatting" and set the rules. Basically, I have about 15 different conditions but all are in columns F through O so I use F:O. For example, if text is exactly YES change the background to green.

The issue is when I add a new row, the formatting stops for that row and the F:O rules are replaced with F1:O15, F17:O59, etc. skipping row 16.

Can I use a script that will never change when rows are added?

Its new sheets. Each row skips a row so I am not sure if that affects it. For example, we have data on row 1, 3, 5, 7, etc. and then add new rows in the middle of these from time to time. This seems to be when the issue happens so I am wondering if a script would be better, and where would it go. Thanks. — Chris

Calvin Gaunce Calvin Gaunce · Accepted Answer · 2015-01-20T19:00:27

You can set up an onEdit trigger that applies the formatting every time you edit the sheet. I've provided an example of a function that would copy the format of cell A1 to all cells in the sheet. This link will bring you to Google's documentation for this type of work. https://developers.google.com/apps-script/reference/spreadsheet/range

Here's the documentation on triggers... https://developers.google.com/apps-script/guides/triggers/

function formatRange(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  var range = sheet.getRange("A1");
  
  range.copyFormatToRange(sheet,1,sheet.getLastColumn(),1,sheet.getLastRow())
  
}

Google Sheets Conditional Formatting changes when new rows added

1 Answers