Conditional Formatting applying to old columns when new columns are copied and inserted.

Brass Contributor

I have columns that have conditional formatting applied using a CONCATENATE formula. 

When I copy and paste the new columns, the formula still uses the old columns and doesn't pick up the new ones. Please can someone help? I have attached a sample file. 

 

For example, columns AK & AL are picking up columns AN & AO when I copy the three columns.

 

milo1234_0-1623401085484.png

 

20 Replies

@milo1234 

See if this works better for you. I replaced all rules with a set of 6 rules for the entire range.

Hi Hans,
Please can you explain the logic around this?
Will it only work if the column A is locked?

@Hans Vogelaar 

 

This is the layout of my spreadsheet and I have attempted to use your 6 rule, however this is not working. Please can you apply to the layout attached and I will mark as best response. Thank you Hans

@milo1234 

The formula refers to A3, not to $A3, so Excel will dynamically adjust it in other columns.

The formula first uses MOD(COLUMN(...),3) to check whether the cell is in a column that is to be formatted conditionally.

If so, it uses OFFSET in combination with MOD(COLUMN(...),3) to determine the two cells to be concatenated. For a cell in column A (or D or G etc.), it is the cell itself and the cell to the right.

For a cell in column B (or E or H etc.) is the cell itself and the cell to the left.

This makes the formula much more dynamic.

@milo1234 

Originally, the range to be formatted began in column A = column 1. 1 = 1 Mod 3.

Now it begins in column I = column 9. 9 = 0 Mod 3.

So everything shifts.

@Hans Vogelaar 

 

Thank you - this makes sense. 

I have applied the formulas to the last column, however there are only a few cells which it does not apply to. Please find attached.

@milo1234 

 

Which cells, for example?

 

Keep in mind that you have applied direct formatting to some of the cells. If none of the 6 rules applies, you'll see the direct formatting.

I have applied your formula to columns AY and AZ however some cells have not been formatted e.g. AY40 and AZ40.

@milo1234 

Please go back to the workbook that I attached. You have somehow managed to completely mess up the rules.

I don't understand how?
I've changed the formatting to follow these 6 rules:
=IF(MOD(COLUMN(I3),3)<>2,VLOOKUP(CONCATENATE(OFFSET(I3,0,-MOD(COLUMN(I3),3)),OFFSET(I3,0,1-MOD(COLUMN(I3),3))),Legend!$B$2:$C$38,2,FALSE)="Blank")
=IF(MOD(COLUMN(I3),3)<>2,VLOOKUP(CONCATENATE(OFFSET(I3,0,-MOD(COLUMN(I3),3)),OFFSET(I3,0,1-MOD(COLUMN(I3),3))),Legend!$B$2:$C$38,2,FALSE)="R")
=IF(MOD(COLUMN(I3),3)<>2,VLOOKUP(CONCATENATE(OFFSET(I3,0,-MOD(COLUMN(I3),3)),OFFSET(I3,0,1-MOD(COLUMN(I3),3))),Legend!$B$2:$C$38,2,FALSE)="AR")
=IF(MOD(COLUMN(I3),3)<>2,VLOOKUP(CONCATENATE(OFFSET(I3,0,-MOD(COLUMN(I3),3)),OFFSET(I3,0,1-MOD(COLUMN(I3),3))),Legend!$B$2:$C$38,2,FALSE)="A")
=IF(MOD(COLUMN(I3),3)<>2,VLOOKUP(CONCATENATE(OFFSET(I3,0,-MOD(COLUMN(I3),3)),OFFSET(I3,0,1-MOD(COLUMN(I3),3))),Legend!$B$2:$C$38,2,FALSE)="AG")
=IF(MOD(COLUMN(I3),3)<>2,VLOOKUP(CONCATENATE(OFFSET(I3,0,-MOD(COLUMN(I3),3)),OFFSET(I3,0,1-MOD(COLUMN(I3),3))),Legend!$B$2:$C$38,2,FALSE)="G")

and ensured the 'Applies To' field reflects the data range from column I onwards.

@milo1234 

I3 must be the active cell in the selection when you create the rules. In your most recent workbook, this is what I see if I select Manage Rules and edit one of the rules:

 

=IF(MOD(COLUMN(XDW3),3)<>2,VLOOKUP(CONCATENATE(OFFSET(XDW3,0,-MOD(COLUMN(XDW3),3)),OFFSET(XDW3,0,1-MOD(COLUMN(XDW3),3))),Legend!$G$2:$H$38,2,FALSE)="Blank")

 

As you see, it does not refer to I3 but to XDW3.

Ah I understand, apologies. I've managed to get it working, however when I change a rating to F1, T1 from the workbook you attached, this does not change to the colour to green.

@Hans Vogelaar 

This is what I see within the attachment you sent back to me :\ very strange

milo1234_0-1623425157397.png

 

@milo1234 

Could you attach the problem workbook?

 

(I'll be away for a couple of hours)

@Hans Vogelaar No problem, please find attached, thanks. 

@milo1234 

I see the problem, but the only conclusion I can draw is that Excel can't handle formatting so many cells. The problem only occurs in some rows of some columns. I have checked the formulas; they return the correct values for the problem cells and yet Excel applies the rules incorrectly...

Hi Hans, so is there no way around this issue?