Fixing conditional formatting despite inserted rows

2
votes

I have a formula that shall be applied to the first 150 rows of a Google Sheets table via conditional formatting. The area it applies to shall be fixed B4:B154 ignoring deleted and inserted rows. As of now the area for the conditional formatting constantly changes, when rows are inserted or deleted.

How can I "fix" the area?

1
google-sheetsgs-conditional-formatting
Exactly. I want the CF to apply just for these exact 150 rows. in that case old row 154, now 155 shall not be subject to CF any more. I'm working on a quite big table with lots of vlookup. I don't want to slow it down any more. There is a lot of people working with the list and they delete and insert rows all the time. this morning areas for the CF looked like this: B4:B30,B32:B106,B108:B147,B149:B251 - quite far away from B4:B154. - An Error occurred... again

1 Answers

0
votes

I suspect it would take an atomic clock to measure the time difference between formatting 151 rows and 150 rows but if you really want to limit the range, while applying whole column references so adding and deleting rows does not fragment the Applies to range, then you could put the restrictions in the formula, if using a formula rule.

For example assuming the requirement is to format the cells in ColumnA containing odd numbers (in Number format) then select ColumnA (for an Applies to range of =$A:$A) and set the formula rule to something like:

=AND(ISODD(A1),ROW()>3,ROW()<155)

My guess is that other changes to your sheet would be a much more effective way to speed up processing (eg INDEX/MATCH can be quicker than VLOOKUP).