Forum Discussion
Keep conditional formatting range when inserting/deleting cells/rows/columns?
Was having the same issue and so my search led to this forum, searched everywhere else and couldn't find the answer, played around with it and finally figured it out. It's actually a pretty simple solution.
Instead of Inserting a column or copying and inserting a column, all you have to do is select the column cells that you want to extend, then at the bottom of the cursor where the plus sign is, click and drag to the right as many columns as needed.
The CF range extended to the last column without creating any extra conditions or messing up the original range.
I have been experimenting with a rough workaround for this problem that applies if you only need to insert new rows OR columns but not both
If you set the conditional format range as entire rows eg ($5:$50), you can then insert new rows within this range to automatically expand it to ($5:$51) without it breaking into pieces. But:
- It only works for conditional formats based on a formula; however, you can mimic (for example) traffic light icons by using character "l" in the Wingdings font and then applying a custom number format of l;l;l;@ on the required range. This forces the values 1, 0 and -1 to all display a filled circle, with the ability to then use conditional formatting to change the colour based on the underlying number value
- You will likely need to include an AND statement in every conditional format formula to stop it from formatting cells in unwanted columns outside your desired range. eg = AND(A$1="Y") and put "Y" in row 1 for all columns that require the format
- Select "Stop if true" on each formula to help improve performance
- The approach will only work on either Rows OR Columns. For example switch to a range of (say) $B:$Z if you wish to add columns within this range