Copying/pasting/cutting/inserting cells manually causes the problem and it is hard to avoid it.
Problem solved through VBA macro.
Instead of copying/pasting/cutting/inserting cells manually, I do it through an Excel macro, which preserves the cell ranges (activated via a button).
Sub addAndBtnClick()
Set Button = ActiveSheet.Buttons(Application.Caller)
With Button.TopLeftCell
ColumnIndex = .Column
RowIndex = Button.TopLeftCell.Row
End With
currentRowIndex = RowIndex
Set Table = ActiveSheet.ListObjects("Table name")
Table.ListRows.Add (currentRowIndex)
Set currentCell = Table.DataBodyRange.Cells(currentRowIndex, Table.ListColumns("Column name").Index)
currentCell.Value = "Cell value"
Call setCreateButtons
End Sub
Sub removeAndBtnClick()
Set Button = ActiveSheet.Buttons(Application.Caller)
With Button.TopLeftCell
ColumnIndex = .Column
RowIndex = Button.TopLeftCell.Row
End With
currentRowIndex = RowIndex
Set Table = ActiveSheet.ListObjects("Table name")
Table.ListRows(currentRowIndex - 1).Delete
End Sub
Sub setCreateButtons()
Set Table = ActiveSheet.ListObjects("Table name")
ActiveSheet.Buttons.Delete
For x = 1 To Table.Range.Rows.Count
For y = 1 To Table.Range.Columns.Count
If y = Table.ListColumns("Column name").Index Then
Set cell = Table.Range.Cells(x, y)
If cell.Text = "Some condition" Then
Set btn = ActiveSheet.Buttons.Add(cell.Left + cell.Width - 2 * cell.Height, cell.Top, cell.Height, cell.Height)
btn.Text = "-"
btn.OnAction = "removeAndBtnClick"
Set btn = ActiveSheet.Buttons.Add(cell.Left + cell.Width - cell.Height, cell.Top, cell.Height, cell.Height)
btn.Text = "+"
btn.OnAction = "addAndBtnClick"
End If
End If
Next
Next
End Sub
To reset formatting (not really needed):
Sub setCondFormat()
Set Table = ActiveSheet.ListObjects("Table name")
Table.Range.FormatConditions.Delete
With Table.ListColumns("Column name").DataBodyRange.FormatConditions _
.Add(xlExpression, xlEqual, "=ISTLEER(A2)") 'Rule goes here
With .Interior
.ColorIndex = 3 'Formatting goes here
End With
End With
...
End Sub
Also note that Cut <kbd>ctrl-x<kbd> will delete the conditional formatting from the cell cut(now the cell has no formatting), and paste the formatting into the new cell. I'll bet someone out there knows how to prevent this? – user5389726598465 – 2017-04-30T18:05:31.987
@user135711 It depends on what you are trying to do. By default, copy and cut both pick up the formulas, formatting, and links of the source cell with cut also removing the same from the source cell. If the intent is to remove the formula/value from the source but to keep the formatting I'll copy/paste the stuff to the destination and then delete the source. Delete (the Del key) only clears the formula or value. It leaves the formatting and links in place. – user3347790 – 2017-05-01T20:13:15.360
1The INDIRECT() function upvoted as an answer in another version of this question doesn't work because it's evaluated to the cells when used in the applies to field just as if you entered cells manually. – user5389726598465 – 2017-05-02T14:51:31.597
https://superuser.com/a/1113566/79488 has a good workaround for cleaning up the fragmented rules if you used copy/pase – Fabian Schmied – 2019-01-03T08:25:09.797