Fragmentacja formatowania warunkowego programu Excel

Często tworzę arkusz z formatowaniem warunkowym i ustawiam zakresy komórek, aby reguły formatowania warunkowego były stosowane tylko raz do zakresu komórek, np.

Make $A$1:$A$30 red and
Make $B$1:$B$30 blue.

Po wstawieniu / usunięciu szeregu wierszy i / lub kolumn zestaw reguł formatowania warunkowego ulega bardzo fragmentacji, przy czym te same reguły są powtarzane dla różnych zakresów. na przykład

Make $A$1:$A$2 red 
Make $A$3:$A$4 red 
Make $A$5:$A$9 red
Make $A$10:$A$20 red 
Make $A$21:$A$30 red
...
Make $B$1:$B$2 blue 
Make $B$3:$B$4 blue
Make $B$5:$B$9 blue
Make $B$10:$B$20 blue 
Make $B$21:$B$30 blue
....

Czy istnieje dobry sposób, aby temu zapobiec, czy też jestem skazany na ręczne czyszczenie zestawów reguł, gdy stają się zbyt nieuporządkowane?

Wstawianie i usuwanie wierszy nie powoduje fragmentacji formatowania warunkowego.

Przyczyną jest kopiowanie / wklejanie między komórkami lub wierszami przy użyciu standardowego kopiowania / wklejania. Rozwiązaniem jest zawsze stosowanie wartości wklejania lub formuły wklejania. W miejscu docelowym kliknij prawym przyciskiem myszy, a sekcja Opcje wklejania zaoferuje 123 (wartości) if (formuły). Nie kopiuj / wklej formatowania, ponieważ powoduje to, że warunki zostaną skopiowane / wklejone, a czasami zostaną podzielone.

Podczas wykonywania standardowego kopiowania / wklejania kopiowane są również formuły warunkowe komórki. Powiedzmy, że masz dwie zasady:
1) Zarób $ 1 $: $ 30 $ czerwony
2) Zarób $ 1 $: $ 30 $ niebieski
Teraz wybierz A10: B10 i skopiuj / wklej to do A20: B20. Program Excel usunie formatowanie warunkowe dla A20: B20 z reguł zastosowanych do tych komórek i doda nowe reguły, które mają formatowanie dla A20: B20. Otrzymasz cztery zasady.
1) Make = $ A $ 20 $ czerwony
2) Make = $ B $ 20 niebieski
3) Make = $ A $ 1: $ A $ 19, $ A $ 21 $ $ 30 czerwony
4) Make = $ B $ 1: $ B 19 $, $ B 21 $: 30 $ B $ niebieski
Gdyby skopiować / wkleić tylko A10 do A20, program Excel zauważyłby tę samą regułę zastosowaną zarówno do źródła, jak i do miejsca docelowego i nie podzieliłby reguł. Excel nie jest wystarczająco inteligentny, aby dowiedzieć się, jak uniknąć fragmentacji, gdy kopiowanie / wklejanie wpływa na dwa lub więcej formatów warunkowych.

Wstawianie i usuwanie wierszy nie powoduje fragmentacji, ponieważ program Excel po prostu rozszerza lub zmniejsza reguły warunków obejmujące obszar, w którym wstawiono lub usunięto wiersz.

Ktoś zasugerował użycie $ Q: $ Q zamiast $ Q $ 1: $ Q 30 USD. To nie pomaga, a mimo to kopiowanie / wklejanie formatowania komórek będzie nadal fragmentowane, jak wspomniano powyżej.

Miał ten sam problem podczas stosowania formatu warunkowego do kolumny tabeli. Podczas dodawania wierszy uznałem, że najlepiej jest zastosować regułę do całej kolumny, używając $A:$Adowolnej kolumny.

(Jest to obejście, więc zamierzam to wyrazić jako komentarz, ale nie mam wystarczającej reputacji).

Niestety wydaje się, że jesteś skazany na czyszczenie zestawów reguł, gdy stają się one nieuporządkowane.

Łatwym sposobem na to jest utworzenie arkusza zawierającego wymagane formatowanie, ale bez danych. Może to być ten sam skoroszyt, co oryginalny arkusz lub inny skoroszyt przechowywany jako szablon.

Gdy chcesz wyczyścić, przejdź do tego arkusza, kliknij prawym przyciskiem myszy przycisk Wybierz wszystko , wybierz Malarza formatów , a następnie kliknij przycisk Wybierz wszystko w oryginalnym arkuszu. Formaty są zastępowane nieskażoną wersją.

Ręczne kopiowanie / wklejanie / wycinanie / wstawianie komórek powoduje problem i trudno go uniknąć.

Problem rozwiązany przez makro VBA.

Zamiast ręcznie kopiować / wklejać / wycinać / wstawiać komórki, robię to poprzez makro Excela, które zachowuje zakresy komórek (aktywowane za pomocą przycisku).

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

Aby zresetować formatowanie (tak naprawdę nie potrzebne):

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