Excel: estendendo automaticamente a formatação condicional para uma nova linha adicionada acima dos dados

Question

Eu tenho vários valores em várias linhas e colunas. Eles são estruturados de forma que cada linha mostre os dados de um mês específico e os valores mais recentes fiquem no topo. Conseqüentemente, quando o atualizo com novos dados, insiro uma nova linha acima dos dados existentes.

Como posso ter certeza de que as regras de formatação condicional que apliquei ao intervalo antigo serão aplicadas ao novo intervalo (com mais uma linha)? Tentei usar tabelas ou intervalos nomeados, mas como adiciono a linha acima dos dados antigos, o Excel não estende automaticamente o intervalo nomeado/a tabela.

editar: esses são os dados existentes

E quero acrescentar janeiro (marcado em vermelho) a isso. Se cada subcategoria tivesse uma linha vazia acima (como a linha 9), eu poderia contornar isso, mas infelizmente não é o caso (veja a linha 5).

Answer 1

Tendo em mente sua estrutura de dados, acho que você só consegue chegar ao que deseja usando VBA. Criei algo semelhante à sua estrutura de dados e escrevi um script que é executado sempre que linhas são adicionadas.

1. Salve seu arquivo comoPasta de trabalho habilitada para macro do Excel (.xlsm)*
2. Abra o Editor VBA por Ctrl+F11
3. No Editor VBA, clique duas vezes na sua planilha no painel esquerdo.
4. Cole este código:

Subplanilha Privada_Change (ByVal Target As Range)
  Dim iTargRowsCnt tão longo
  Dim ofCond As FormatCondition
  Dim sFCondRanges() como string
  Dim i enquanto
  Dim blnFCondRangeModified As Boolean
  Dim sNewAppliesTo como string

  Se Target.Address = Target.EntireRow.Address Então
    iTargRowsCnt = Target.Rows.Count
    Para cada ofCond em Me.UsedRange.FormatConditions
      sFCondRanges() = Dividir(oFCond.AppliesTo.Address, ",")
      Para i = LBound(sFCondRanges) Para UBound(sFCondRanges)
        Se Not Intersect(Me.Range(sFCondRanges(i))(1), Target(1).EntireRow.Offset(iTargRowsCnt)) não for nada, então
          sFCondRanges(i) = Range(Range(sFCondRanges(i)), Range(sFCondRanges(i)).Offset(-iTargRowsCnt)).Endereço
          blnFCondRangeModified = Verdadeiro
        Fim se
      Proximo eu
      Se blnFCondRangeModified então
        Para i = LBound(sFCondRanges) Para UBound(sFCondRanges)
          sNewAppliesTo = sNewAppliesTo & sFCondRanges(i) & ","
        Próximo
        sNewAppliesTo = Esquerda(sNewAppliesTo, Len(sNewAppliesTo) - 1)
        intervalo oFCond.ModifyAppliesToRange (sNewAppliesTo)
        blnFCondRangeModified = Falso
        sNewAppliesTo = ""
      Fim se
    Próximo 'oFCond
  Fim se

Finalizar sub

Este código será executado automaticamente sempre que você inserir linhas. Mas tenha cuidado ao usar o VBA. Economize com mais frequência, algo inesperado pode ocorrer, embora eu tenha tentado mitigar os riscos. Por exemplo, é difícil projetar um código que funcione bem com a funcionalidade Desfazer. Nesse caso, os formatos condicionais ficariam distorcidos se você inserisse linhas e depois usasse Desfazer.

Answer 2

Eu ficaria apenas com intervalos. Inclua uma linha extra na parte superior ao aplicar a formatação condicional e, em seguida, oculte-a ou faça o que quiser com ela visualmente (eu anotaria em algum lugar seu propósito como linha de buffer pessoalmente, para que não seja removida por engano).

Outro método seria copiar a linha superior e colar como formatação sobre sua nova linha (não em um computador no momento, é necessário verificar se isso funciona para formatação condicional ou não) . EDIT: trabalho confirmado para formatação condicional no O365 Business Plus: Excel 2016.

Answer 3

use o intervalo 1:1000, ele pode mudar para o intervalo completo (A1:AZ1000 ou algo parecido), mas será atualizado à medida que você adicionar mais linhas ou colunas, portanto permanecerá no intervalo completo

Answer 4

Depois de enfrentar um desafio semelhante, descobri que simplesmente referir-se ao nome da tabela na entrada "Aplicar a:" ajusta-se automaticamente a qualquer alteração na tabela, incluindo a adição de linhas :).