Forum Discussion
Solved
Keep conditional formatting range when inserting/deleting cells/rows/columns?
Hi, I sometimes use conditional formatting. For each entry, there's a cell range that it applies to. Often I need it to be used on the entire sheet, or at least a large range of it, i.e. all rows...
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 was looking for a way to colour the weekend rows of a vertical timeline without moving when adding or deleting cells in a column. This is my solution:
Create a separate conditional format rule for each column (yes, it was time consuming)
- The range should be for that column only
- Use an offset formula to reference the days of the week cell.
In my case, days of the week were identified in column B.
so the condition formatting formula for column C would be =or(offset($C2,0,-1)="Saturday",offset($C2,0,-1)="Sunday")
the range would be =$C$2:$C$100
I've tested this and can confirm it works. Perhaps an offset formula can work for other scenarios.
Create a separate conditional format rule for each column (yes, it was time consuming)
- The range should be for that column only
- Use an offset formula to reference the days of the week cell.
In my case, days of the week were identified in column B.
so the condition formatting formula for column C would be =or(offset($C2,0,-1)="Saturday",offset($C2,0,-1)="Sunday")
the range would be =$C$2:$C$100
I've tested this and can confirm it works. Perhaps an offset formula can work for other scenarios.