Guru do Excel e Power BI • Células coloridas no topo da tabela

Página 1 de 1

Células coloridas no topo da tabela

Enviado: 18 Dez 2018 às 17:44

por SandroLima

Boa tarde, pessoal

Preciso de ajuda em duas questões:

Primeira:
Qual a forma de fazer para que as células que estejam coloridas por obedecerem determinada condição fiquem no topo da tabela?

Tenho uma tabela e as células das colunas "Registro" e "Data" possuem formatação condicional.

Como faço para que as linhas da tabela que tenham células coloridas nessas duas colunas ("Registro" e "Data") fiquem no topo da tabela automaticamente ao serem preenchidas?

Segunda:
As regras da formatação condicional se aplicam a todas as Células das colunas "Registro" e "Data"... porém cada vez que rodo a macro para inserir uma nova linha ele cria uma nova linha de formatação condicional lá no gerenciamento de regras da formatação condicional... regra esta só para a nova linha que foi adicionada...
Daqui a pouco terei milhares de regras... uma para cada linha.
Como corrigir isso?

Segue planilha anexa para elucidação.

Obrigado a quem puder colaborar.

Re: Células coloridas no topo da tabela

Enviado: 19 Dez 2018 às 14:49

por SandroLima

Boa tarde, pessoal.

Uma parte do problema resolvi com o seguinte código:

Código: Selecionar todos

With TabelaAtividades.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("TB_AtividadesDiarias[[#All],[Registro]]"), SortOn:= _
        xlSortOnCellColor, Order:=xlDescending, DataOption:=xlSortNormal
        .SortOnValue.Color = RGB(255, 235, 156)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Ele atende para a coluna "Registro". Porém não sei ainda como resolver para a coluna "Data" já que possui mais de uma formatação condicional.

Também ainda não consegui resolver o fator de adicionar uma nova regra de formatação condicional no gerenciador de regras cada vez que o código roda.

Segue a planilha ("PLAN2") com o resultado pretendido.

Continuo aguardando ajuda.

Re: Células coloridas no topo da tabela

Enviado: 19 Dez 2018 às 15:18

por osvaldomp

SandroLima escreveu:... porém cada vez que rodo a macro para inserir uma nova linha ele cria uma nova linha de formatação condicional ...
Daqui a pouco terei milhares de regras...
Verdade! E vai comprometer a performance da planilha!

Sugestão: no início do código que insere a linha insira um comando para limpar a FC de toda a Tabela, na sequência deixe o comando que insere a linha e por último coloque um comando para reaplicar a FC na Tabela.

Re: Células coloridas no topo da tabela

Enviado: 19 Dez 2018 às 16:08

por SandroLima

Boa tarde, Osvaldomp.

Obrigado pelo apontamento.

Entendo que seria essa a alternativa mais viável... mas não sei como fazer isso ainda.

Pode me ajudar?

Re: Células coloridas no topo da tabela

Enviado: 19 Dez 2018 às 16:26

por osvaldomp

Fiz esse código há um tempo. Ele aplica cores. Veja se consegue aproveitar as estruturas. O gravador poderá te ajudar também.

Código: Selecionar todos

Sub AplicaFCNomesSituação()
 Dim nomes As Range, situação As Range
 Set nomes = Range("B2:B" & Cells(Rows.Count, 2).End(3).Row)
 Set situação = Range("L2:L" & Cells(Rows.Count, 12).End(3).Row)
 
 'limpa as regras de FC da planilha
 Cells.FormatConditions.Delete
 
 'aplica FC na coluna B com base no nome que estiver na célula - JOÃO ou MARIA
 With nomes.FormatConditions
  .Add Type:=xlTextString, String:="JOÃO" , TextOperator:=xlContains
    With .Item(.Count).Interior
     .ThemeColor = xlThemeColorAccent3
     .TintAndShade = 0.399945066682943
    End With
    
  .Add Type:=xlTextString, String:="MARIA",  TextOperator:=xlContains
    With .Item(.Count).Interior
     .Color = 49407
    End With
 End With
 
'aplica FC na coluna L via fórmula
 With situação.FormatConditions
    .Add Type:=xlExpression, Formula1:= _
        "=E($I2<>"""";$I2=1)"
    With .Item(.Count).Interior
        .Color = 255
    End With
    
    .Add Type:=xlExpression, Formula1:= _
        "=E($I2<>"""";$I2>1)"
    With .Item(.Count).Interior
        .Color = 5296274
    End With
 End With
End Sub

Re: Células coloridas no topo da tabela

Enviado: 19 Dez 2018 às 17:27

por SandroLima

Estou na tentativa aqui, Osvaldomp.

Consegui transcrever a primeira sugestão conforme seu código... deu certo... e ficou assim:

Código: Selecionar todos

TabelaAtividades.DataBodyRange.FormatConditions.Delete

A segunda parte... a de aplicar cores com VBA... tentei algo assim:

Código: Selecionar todos

With TabelaAtividades.ListColumns("Registro").DataBodyRange.FormatConditions
        .Add Type:=xlExpression, Formula1:= _
        "=E(F$11="Paciente";(EXT.TEXTO($I10;1;LOCALIZAR("ano";$I10)-1)*1>=8);OU($G10="";$G10="-"))"
        With .Item(.Count).Interior
            .Color = RGB(255, 235, 156)
        End With
    End With

Mas parece estar apresentando erro na sintaxe da fórmula (Está com a cor vermelha na guia Desenvolvedor).
E coloquei apenas para colorir o interior da célula... eu não soube como acrescentar a parte da cor da fonte.

Segue planilha com o código aplicado.

Re: Células coloridas no topo da tabela

Enviado: 19 Dez 2018 às 17:54

por osvaldomp

SandroLima escreveu: Mas parece estar apresentando erro na sintaxe da fórmula (Está com a cor vermelha na guia Desenvolvedor).
As aspas que fazem parte da fórmula devem ser dobradas. Veja no exemplo que coloquei no post anterior.
Para testar e acertar a sintaxe faça uma sub somente para inserir a fórmula em uma célula qualquer e depois de acertar então coloque-a no código que insere a FC. Algo conforme abaixo:
Código: Selecionar todos
Sub InsereFórmula()
ActiveCell.Value = "suafórmula"
End Sub
E coloquei apenas para colorir o interior da célula... eu não soube como acrescentar a parte da cor da fonte.
Experimente assim:
Código: Selecionar todos
With .Item(.Count)
  .Interior.Color = RGB(255, 235, 156)
  .Font.Color=RGB (0,0,0)
End With

Re: Células coloridas no topo da tabela

Enviado: 19 Dez 2018 às 19:29

por SandroLima

O código inteiro rodou normalmente sem erros... excluiu a formatação existente... executou as demais rotinas do código... só não inseriu a formatação novamente.

Código: Selecionar todos

With TabelaAtividades.ListColumns("Registro").DataBodyRange.FormatConditions
        .Add Type:=xlExpression, Formula1:= _
        "=E(F$11=""Paciente"";(EXT.TEXTO($I11;1;LOCALIZAR(""ano"";$I11)-1)*1>=8);OU($G11="""";$G11=""-""))"
        With .Item(.Count)
            .Interior.Color = RGB(255, 235, 156)
            .Font.Color = RGB(46, 139, 87)
        End With
    End With

Mas rodou sem erro aparentemente. O que pode ser?

Re: Células coloridas no topo da tabela

Enviado: 19 Dez 2018 às 21:46

por osvaldomp

SandroLima escreveu:O código inteiro rodou normalmente sem erros...
O código não trava por erro porque você colocou um comando para não travar ~~~> On Error Resume Next.
Se você remover esse comando o código irá travar na parte que tenta ordenar pela cor.
Aliás porque você colocou esse comando? Não vejo necessidade, pior, não recomendável nesse código.

excluiu a formatação existente... só não inseriu a formatação novamente.
Exclui e reinsere a FC. Após rodar o código selecione uma célula ative a FC e você verá que a Regra foi aplicada. Não está pintando as células pois me parece que a sua fórmula precisa de correção: esta parte ~~~> "=E(F$11=""Paciente""; ... ~~~> não deveria ser ~~~>"=E($F11=""Paciente""; ... ?
Não examinei as demais partes. Sugiro que você aplique a FC manualmente e só depois que estiver funcionando manualmente faça os ajustes no código.

obs.
1. Set TabelaAtividades = Nothing ~~~> ao encerrar a execução o valor da variável se escoa, então esse comando é desnecessário
2. Ulinha ~~~> o código atribui um valor a essa variável e em seguida insere uma linha na Tabela, em consequência da nova linha o valor da variável não fica desatualizado ?

Re: Células coloridas no topo da tabela

Enviado: 20 Dez 2018 às 16:18

por SandroLima

Boa tarde, Osvaldomp. Boa tarde, pessoal do fórum.

Tudo indo bem aqui na adequação do código da planilha

Com a dica que me deu ele parou de incluir novas regras no gerenciador de regras da FC.

O código da FC dessa coluna na minha planilha completa ficou da seguinte maneira:

Código: Selecionar todos

With TabelaAtividades.ListColumns("Registro").DataBodyRange.FormatConditions
        .Add Type:=xlExpression, Formula1:= _
        "=E($I11=""Paciente"";(EXT.TEXTO($N11;1;LOCALIZAR(""ano"";$N11)-1)*1>=8);OU($L11="""";$L11=""-""))"
        .Add Type:=xlExpression, Formula1:= _
        "=OU($W11=""Aguardando pagamento"";$W11="""";$X11="""")"
        With .Item(.Count)
        .Interior.Color = RGB(255, 235, 156)
        .Font.Color = RGB(156, 101, 0)
        End With
    End With

E fez a rotina desejada... limpou a FC e depois aplicou a FC na coluna desejada.

Quando acrescentei mais uma linha de código da FC novamente deixou de aplicar a FC desejada na coluna.

Código: Selecionar todos

.Add Type:=xlExpression, Formula1:= _
        "=E($AA11<>"""";$AA11<>""-"";OU($AB11="""";$AB11=""-""))"
        With .Item(.Count)

O código ficou dessa maneira:

Código: Selecionar todos

With TabelaAtividades.ListColumns("Registro").DataBodyRange.FormatConditions
        .Add Type:=xlExpression, Formula1:= _
        "=E($I11=""Paciente"";(EXT.TEXTO($N11;1;LOCALIZAR(""ano"";$N11)-1)*1>=8);OU($L11="""";$L11=""-""))"
        .Add Type:=xlExpression, Formula1:= _
        "=OU($W11=""Aguardando pagamento"";$W11="""";$X11="""")"
        .Add Type:=xlExpression, Formula1:= _
        "=E($AA11<>"""";$AA11<>""-"";OU($AB11="""";$AB11=""-""))"
        With .Item(.Count)
        .Interior.Color = RGB(255, 235, 156)
        .Font.Color = RGB(156, 101, 0)
        End With
    End With

Só deixou de funcionar quando eu acrescentei a terceira FC.
Fiz algo de errado agora? Ou é por causa da quantidade de FC?

Re: Células coloridas no topo da tabela

Enviado: 20 Dez 2018 às 19:11

por osvaldomp

Esse código que você colocou por último aplica 3 Regras na FC, no entanto ele aplica o Formato somente na última Regra.
Rode o código, em seguida selecione qualquer célula na coluna B da Tabela e ative a FC. Você verá que as 3 Regras foram aplicadas, no entanto, no campo que indica o Formato de cada Regra, só aparece a cor do preenchimento na terceira Regra e nas duas primeiras aparece "sem definição de formato".

Veja novamente o exemplo que eu coloquei no post #39417 :
primeira Regra ~~~> critério "JOÃO" ~~~> na sequência o formato desejado (a cor do preenchimento);
segunda Regra ~~~> critério "MARIA" ~~~> idem.

Ou seja, para cada Regra devem ser inseridos o critério e na sequência a formatação desejada.

Re: Células coloridas no topo da tabela

Enviado: 21 Dez 2018 às 20:57

por SandroLima

Obrigado, Osvaldomp.

Era isso mesmo... apliquei como orientou e funcionou e não acrescentou mais regras na FC.

Muito obrigado.

Agora vou aplicar nas demais colunas que também possuem a FC... em uma das colunas inclusive as células com a FC tem borda... qual a linha de código que devo acrescentar aqui?

Código: Selecionar todos

With TabelaAtividades.ListColumns("Registro").DataBodyRange.FormatConditions
        .Add Type:=xlExpression, Formula1:= _
        "=E($I11=""Paciente"";(EXT.TEXTO($N11;1;LOCALIZAR(""ano"";$N11)-1)*1>=8);OU($L11="""";$L11=""-""))"
        With .Item(.Count)
        .Interior.Color = RGB(255, 235, 156)
        .Font.Color = RGB(156, 101, 0)
        End With
    End With

E mais uma vez obrigado.

Re: Células coloridas no topo da tabela

Enviado: 21 Dez 2018 às 21:58

por osvaldomp

SandroLima escreveu:... em uma das colunas inclusive as células com a FC tem borda... qual a linha de código que devo acrescentar aqui?
Acrescente esta linha ~~~> .Borders.LineStyle = xlContinuous

Código: Selecionar todos

With TabelaAtividades.ListColumns("Registro").DataBodyRange.FormatConditions
  .Add Type:=xlExpression, Formula1:= _
  "=E($I11=""Paciente"";(EXT.TEXTO($N11;1;LOCALIZAR(""ano"";$N11)-1)*1>=8);OU($L11="""";$L11=""-""))"
   With .Item(.Count)
    .Interior.Color = RGB(255, 235, 156)
    .Font.Color = RGB(156, 101, 0)
    .Borders.LineStyle = xlContinuous
   End With
End With

Re: Células coloridas no topo da tabela

Enviado: 22 Dez 2018 às 01:05

por SandroLima

Muito obrigado , Osvaldomp...

Mas visualmente não mudou nada. E olhe que tentei de diversas maneiras, inclusive somente da maneira que você descreveu.
Queria uma borda vermelha por exemplo. Somente nas células que atendessem à condição.

Código: Selecionar todos

With TabelaAtividades.ListColumns("CPF / CNPJ").DataBodyRange.FormatConditions
        .Add Type:=xlExpression, Formula1:= _
        "=E((EXT.TEXTO($I11;1;LOCALIZAR(""ano"";$I11)-1)*1>=8);OU($K11="""";$K11=""-""))"
        With .Item(.Count)
        .Interior.Color = RGB(255, 235, 156)
        .Font.Color = RGB(156, 101, 0)
        '.BorderAround , , , vbBlack
        .Borders.LineStyle = xlContinuous
        .Borders.ColorIndex = 0
        .Borders.Weight = RGB(156, 101, 0)
        '.Borders(xlEdgeTop).Color = RGB(250, 22, 41)
        '.Borders(xlEdgeBottom).Color = RGB(250, 22, 41)
        End With
    End With

Re: Células coloridas no topo da tabela

Enviado: 22 Dez 2018 às 08:21

por osvaldomp

Selecione uma célula vazia e sem qualquer formatação aplicada a ela e rode o código abaixo.
Em seguida digite joão na célula e veja o resultado.

Código: Selecionar todos

Sub BordasFC()
 With ActiveCell.FormatConditions
  .Add Type:=xlTextString, String:="JOÃO", TextOperator:=xlContains
   With .Item(.Count)
    .Interior.Color = RGB(255, 235, 156)
    .Font.Color = RGB(156, 101, 0)
    .Borders.LineStyle = xlContinuous
    .Borders.Color = vbRed
   End With
 End With
End Sub

Re: Células coloridas no topo da tabela

Enviado: 22 Dez 2018 às 15:43

por SandroLima

Bom dia, Osvaldomp

Funciona perfeitamente em qualquer área da tabela. Menos na coluna "CPF / CNPJ" que preciso. Deve ser algo com o apontamento dela no código.

O cabeçalho da coluna é CPF / CNPJ.

Esse código executa:

Código: Selecionar todos

TabelaAtividades.ListColumns("Registro").DataBodyRange.FormatConditions.Delete

Mas esse não:

Código: Selecionar todos

TabelaAtividades.ListColumns("CPF / CNPJ").DataBodyRange.FormatConditions.Delete

Como fica a nomenclatura da coluna no código? Ela possui esse espaço mesmo entre os nomes e a barra de separação "/".

Re: Células coloridas no topo da tabela

Enviado: 22 Dez 2018 às 16:03

por osvaldomp

Putz ... eu achei que com o assunto Bordas estávamos finalizando o tópico, mas agora voltamos para o Delete

Re: Células coloridas no topo da tabela

Enviado: 22 Dez 2018 às 17:09

por SandroLima

Pode ser tb rsrs

Fato é que nesse trecho... no apontamento da coluna (CPF / CNPJ) não está formatando:

É por causa do nome no cabeçalho da coluna que tem espaços?

Código: Selecionar todos

With TabelaAtividades.ListColumns("CPF / CNPJ").DataBodyRange.FormatConditions

Código completo para a formatação da coluna:

Código: Selecionar todos

With TabelaAtividades.ListColumns("CPF / CNPJ").DataBodyRange.FormatConditions
        .Add Type:=xlExpression, Formula1:= _
        "=E((EXT.TEXTO($I11;1;LOCALIZAR(""ano"";$I11)-1)*1>=8);OU($K11="""";$K11=""-""))"
        With .Item(.Count)
        .Interior.Color = RGB(255, 235, 156)
        .Font.Color = RGB(156, 101, 0)
        .Borders.LineStyle = xlContinuous
        .Borders.Color =  vbRed
        End With
    End With

Re: Células coloridas no topo da tabela

Enviado: 22 Dez 2018 às 17:52

por osvaldomp

Repetindo sugestões anteriores:
a) aplique a FC manualmente e só passe para o código depois que manualmente funcionar;
b) utilize o gravador