Google Sheets Conditional Formatting - Column comparison w/ changing data

Question

I have a code generated spreadsheet which has the following data:

Col:A    B        C        D        

Sect   | Lbl    | Data1  | Data2      
===================================
Sec1   | Lbl1   | 1      | >50       
-----------------------------------
Sec2   | Lbl2   | 2      | 1

I have a conditional format rule in place to say that if Data1 is greater than Data2 then make the cell background colour red in the Data1 column. If the data is >50 I extract the 50 in my formula. I ignore the first row and the first 2 columns as they aren't needed for this formatting.

Apply to range: C2:Z1000

Custom Formula is: =AND((1*REGEXEXTRACT(D2,"\d+"))<(1*REGEXEXTRACT(C2,"\d+")))

This seems to work ok.

Next step is some new data is inserted between column B and C in the existing spreadsheet (my newest data is always on the left)

Col:A    B        C        D         E      

Sect   | Lbl    | NEWData| Data1   | Data2    
==========================================
Sec1   | Lbl1   | 9      | 1       | >50       
------------------------------------------
Sec2   | Lbl2   | 3      | 2       | 1

So as you can see, the new data is now in column C and everything has shifted over 1 column. The rule now affects column D (with no change to the above set up). I want it, however, to affect all columns C onwards so:

NEWData value is red if greater than Data1 value on this row
Data1 value is red if greater than Data2 value on this row
Data2 will always be unformatted as it has no value to compare on its right-hand side.

Every time I add a new column (in column C's position) I want the colouring to be updated for all applicable columns data.

Side Note: I also have another similar rule which colours the cell green if the value is 'less than' as opposed to 'greater than'. This will also be applied once I get this rule working.

If you highlight column C and insert 1 column left, doesn't it copy the conditional formatting formulas to the new column and adjust the formulas in the existing columns, or am I missing something? — Tom Sharpe
not sure how you imagine pulling this off by adding columns to the left when you have a formula that specifically looks into only 2 columns — player0
Well advice is needed really. I want to either (a) adjust the formula to iterate across all columns or (b) perhaps add a new formula each time I insert a column via the code. I’m new to the conditional formatting but I thought if the prefix of $ isnt added the formula does/should iterate across all the specified range? — scgough

scgough scgough · Accepted Answer · 2019-05-15T11:33:50

I've finally figured this out. The problem seemed to be in the formatting (or assumed format) of the data being entered into the new column.

My code inserted values work with the rules (see below) but when I manually typed in values to a new column (to test the formula) the rule wasn't applied. Manually copying and pasting a value into a new column DID work though. Very irritating.

Anyway, I have simplified my rules:

BG color = red if this value greater than the value to the right

Range: C2:Z
Formula: =GT(1*REGEXEXTRACT(C2,"\d+"),1*REGEXEXTRACT(D2,"\d+"))

BG color = green if this value less than the value to the right

Range: C2:Z
Formula: =LT(1*REGEXEXTRACT(C2,"\d+"),1*REGEXEXTRACT(D2,"\d+"))

Google Sheets Conditional Formatting - Column comparison w/ changing data

1 Answers