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

%3CLINGO-SUB%20id%3D%22lingo-sub-2438308%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20applying%20to%20old%20columns%20when%20new%20columns%20are%20copied%20and%20inserted.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2438308%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20columns%20that%20have%20conditional%20formatting%20applied%20using%20a%20CONCATENATE%20formula.%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20copy%20and%20paste%20the%20new%20columns%2C%20the%20formula%20still%20uses%20the%20old%20columns%20and%20doesn't%20pick%20up%20the%20new%20ones.%20Please%20can%20someone%20help%3F%20I%20have%20attached%20a%20sample%20file.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20columns%20AK%20%26amp%3B%20AL%20are%20picking%20up%20columns%20AN%20%26amp%3B%20AO%20when%20I%20copy%20the%20three%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22milo1234_0-1623401085484.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F288015iD6DED73A292A3CCD%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22milo1234_0-1623401085484.png%22%20alt%3D%22milo1234_0-1623401085484.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2438308%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2438750%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20applying%20to%20old%20columns%20when%20new%20columns%20are%20copied%20and%20inserted.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2438750%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F972444%22%20target%3D%22_blank%22%3E%40milo1234%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20if%20this%20works%20better%20for%20you.%20I%20replaced%20all%20rules%20with%20a%20set%20of%206%20rules%20for%20the%20entire%20range.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2438923%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20applying%20to%20old%20columns%20when%20new%20columns%20are%20copied%20and%20inserted.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2438923%22%20slang%3D%22en-US%22%3EHi%20Hans%2C%3CBR%20%2F%3EPlease%20can%20you%20explain%20the%20logic%20around%20this%3F%3CBR%20%2F%3EWill%20it%20only%20work%20if%20the%20column%20A%20is%20locked%3F%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2439007%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20applying%20to%20old%20columns%20when%20new%20columns%20are%20copied%20and%20inserted.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2439007%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20layout%20of%20my%20spreadsheet%20and%20I%20have%20attempted%20to%20use%20your%206%20rule%2C%20however%20this%20is%20not%20working.%20Please%20can%20you%20apply%20to%20the%20layout%20attached%20and%20I%20will%20mark%20as%20best%20response.%20Thank%20you%20Hans%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2439010%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20applying%20to%20old%20columns%20when%20new%20columns%20are%20copied%20and%20inserted.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2439010%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F972444%22%20target%3D%22_blank%22%3E%40milo1234%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20formula%20refers%20to%20A3%2C%20not%20to%20%24A3%2C%20so%20Excel%20will%20dynamically%20adjust%20it%20in%20other%20columns.%3C%2FP%3E%0A%3CP%3EThe%20formula%20first%20uses%20MOD(COLUMN(...)%2C3)%20to%20check%20whether%20the%20cell%20is%20in%20a%20column%20that%20is%20to%20be%20formatted%20conditionally.%3C%2FP%3E%0A%3CP%3EIf%20so%2C%20it%20uses%20OFFSET%20in%20combination%20with%20MOD(COLUMN(...)%2C3)%20to%20determine%20the%20two%20cells%20to%20be%20concatenated.%20For%20a%20cell%20in%20column%20A%20(or%20D%20or%20G%20etc.)%2C%20it%20is%20the%20cell%20itself%20and%20the%20cell%20to%20the%20right.%3C%2FP%3E%0A%3CP%3EFor%20a%20cell%20in%20column%20B%20(or%20E%20or%20H%20etc.)%20is%20the%20cell%20itself%20and%20the%20cell%20to%20the%20left.%3C%2FP%3E%0A%3CP%3EThis%20makes%20the%20formula%20much%20more%20dynamic.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2439039%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20applying%20to%20old%20columns%20when%20new%20columns%20are%20copied%20and%20inserted.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2439039%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F972444%22%20target%3D%22_blank%22%3E%40milo1234%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOriginally%2C%20the%20range%20to%20be%20formatted%20began%20in%20column%20A%20%3D%20column%201.%201%20%3D%201%20Mod%203.%3C%2FP%3E%0A%3CP%3ENow%20it%20begins%20in%20column%20I%20%3D%20column%209.%209%20%3D%200%20Mod%203.%3C%2FP%3E%0A%3CP%3ESo%20everything%20shifts.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2439183%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20applying%20to%20old%20columns%20when%20new%20columns%20are%20copied%20and%20inserted.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2439183%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20-%20this%20makes%20sense.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20applied%20the%20formulas%20to%20the%20last%20column%2C%20however%20there%20are%20only%20a%20few%20cells%20which%20it%20does%20not%20apply%20to.%20Please%20find%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2439216%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20applying%20to%20old%20columns%20when%20new%20columns%20are%20copied%20and%20inserted.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2439216%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F972444%22%20target%3D%22_blank%22%3E%40milo1234%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhich%20cells%2C%20for%20example%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EKeep%20in%20mind%20that%20you%20have%20applied%20direct%20formatting%20to%20some%20of%20the%20cells.%20If%20none%20of%20the%206%20rules%20applies%2C%20you'll%20see%20the%20direct%20formatting.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2439242%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20applying%20to%20old%20columns%20when%20new%20columns%20are%20copied%20and%20inserted.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2439242%22%20slang%3D%22en-US%22%3EI%20have%20applied%20your%20formula%20to%20columns%20AY%20and%20AZ%20however%20some%20cells%20have%20not%20been%20formatted%20e.g.%20AY40%20and%20AZ40.%3C%2FLINGO-BODY%3E
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?