Fragmentação de formatação condicional do Excel

Question

Geralmente, eu crio uma planilha com formatação condicional e defino os intervalos de células para que as regras de formatação condicionais sejam aplicadas apenas uma vez a um intervalo de células, por exemplo,

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

Após inserir / excluir um número de linhas e / ou colunas, o conjunto de regras de formatação condicional fica muito fragmentado, com as mesmas regras repetidas para intervalos diferentes. por exemplo.

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
....

Existe uma boa maneira de prevenir isso, ou estou condenada a limpar os conjuntos de regras manualmente quando eles ficam muito confusos?

    

Answer 1

Inserir e excluir linhas não faz com que a formatação condicional seja fragmentada.

A causa é copiar / colar entre células ou linhas usando o copiar / colar padrão. A correção é sempre usar o valor de colar ou a fórmula de colar. No destino, clique com o botão direito do mouse e a seção Opções de Colagem oferecerá 123 (valores) ef (fórmulas). Não copie / cole formatação, pois isso faz com que as condições sejam copiadas / coladas e, às vezes, elas serão fragmentadas.

Quando você faz um copiar / colar padrão, ele também copia as fórmulas condicionais da célula. Digamos que você tenha duas regras:
1) Faça $ A $ 1: $ A $ 30 vermelho
2) Faça $ B $ 1: $ B $ 30 azul
Agora selecione A10: B10 e copie / cole para A20: B20. O que o Excel fará é excluir a formatação condicional de A20: B20 das regras aplicadas a essas células e adicionar novas regras que tenham a formatação para A20: B20. Você acaba com quatro regras.
1) Make = $ A $ 20 vermelho
2) Make = $ B $ 20 azul
3) Make = $ A $ 1: $ A $ 19, $ A $ 21: $ A $ 30 vermelho
4) Make = $ B $ 1: $ B $ 19, $ B $ 21: $ B $ 30 azul
Se você tivesse copiado / colado apenas A10 para A20, o Excel teria notado a mesma regra aplicada à origem e ao destino e não fragmentaria as regras. O Excel não é inteligente o suficiente para descobrir como evitar a fragmentação quando o seu copiar / colar afeta dois ou mais formatos condicionais.

Inserir e excluir linhas não causa fragmentação, pois o Excel simplesmente expande ou reduz as regras de condição que cobrem a área em que você inseriu ou excluiu a linha.

Alguém sugeriu usar $ Q: $ Q em vez de $ Q $ 1: $ Q $ 30. Isso não ajuda e você ainda obterá a fragmentação quando copiar / colar a formatação da célula, conforme mencionado acima.

    

Answer 2

Tive o mesmo problema ao aplicar o formato condicional a uma coluna de tabela. Ao adicionar linhas, descobri que é melhor aplicar a regra à coluna inteira usando $A:$A ou qualquer coluna.

    

Answer 3

Copiar / colar / recortar / inserir células manualmente causa o problema e é difícil evitá-lo.

Problema resolvido através da macro VBA.

Em vez de copiar / colar / cortar / inserir células manualmente, faço isso através de uma macro do Excel, que preserva as faixas de células (ativadas por meio de um botão).

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

Para redefinir a formatação (não é realmente necessário):

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

    

Answer 4

(Esta é uma solução alternativa, então eu colocaria isso como um comentário, mas não tenho reputação suficiente.)

Infelizmente, parece que você está fadado a limpar os conjuntos de regras quando ficarem confusos.

Uma maneira fácil de fazer isso é criar uma planilha contendo a formatação desejada, mas sem dados. Isso pode estar na mesma pasta de trabalho da sua planilha original ou em outra pasta de trabalho mantida como modelo.

Quando precisar limpar, vá para esta planilha, clique com o botão direito do mouse no botão Select All , escolha o Format Painter e clique no Select All na sua planilha original. Os formatos são sobrescritos com a versão não contaminada.