I’m having trouble with conditional formatting in Google Sheets. I select my entire worksheet by clicking the corner between column A and row 1. Then I go to the Format menu and choose Conditional Formatting to create my rules.
I have around 15 different formatting conditions that apply to columns F through O, so I set the range as F:O. One example rule is changing the background color to green when a cell contains exactly “YES”.
The problem happens when I insert new rows anywhere in my sheet. The conditional formatting gets messed up and stops working for the newly inserted row. Instead of keeping the original F:O range, it splits into multiple ranges like F1:O15, F17:O59, completely skipping the new row 16.
Is there a way to use Google Apps Script to create conditional formatting that won’t break when I add new rows? I need something that will always apply to the entire range regardless of row insertions.
Had the same problem in Google Sheets a few months ago - super annoying. Google Sheets messes up conditional formatting when you insert new rows, and it breaks your existing ranges. I fixed it by changing my range from F:O to something like F1:O10000. Now the formatting stays put no matter how many rows I add. Just make sure your fixed range is big enough for what you normally do. Yeah, you’ll have to redo your existing rules, but it’s worth it to avoid this headache later.
Google Apps Script works great for this. I wrote a simple script that reapplies conditional formatting after adding rows. Use the onEdit trigger to catch when rows get added, then recreate your formatting with getRange() and addConditionalFormatRule(). The trick is storing your formatting conditions as variables in the script instead of relying on UI ranges. Mine runs whenever someone changes the sheet structure. Takes 10 minutes to set up but saves hours of manual fixes. Just test it on a copy first - scripts can overwrite your existing formatting.
use named ranges - they’re way better than regular ranges. just create a named range for F:O in the name box, then use that in your conditional formatting. when you add rows, the named range updates automatically and keeps everything formatted correctly. much easier than scripts or massive fixed ranges.