Guru do Excel e Power BI

Bom dia, usuários e colaboradores do fórum.

Na planilha em anexo tenho a Macro "Formata_ColunaRegistro".

Como eu faço para que a Macro execute após atendidas as duas condições a seguir:
(a macro deve ser executada caso atenda qualquer uma das condições)
1) Todas as células de uma linha da tabela forem preenchidas;
2) Se houver alteração na cor de preenchimento de uma célula da linha que estiver totalmente preenchida para a condição de sem preenchimento de cor.
Por exemplo, na coluna [Plan Condomínio] ou na coluna [Plan Recibos] ao passar do status "Aguardando envio" para "Ok" ela perde a cor de preenchimento (amarela) (formatação condicional) e fica sem cor de preenchimento.
Na coluna [Status Pgto] ao passar de Aguardando pagamento" para "Pago" a célula também perde a cor de preenchimento.
Nessa condição rodar a Macro "Formata_ColunaRegistro".

Segue planilha para verificação.

Veja se atende.

Código: Selecionar todos

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lobTab              As ListObject
    
    Set lobTab = Wsh_AtivDiarias.ListObjects("TB_AtivDiarias")
    
    With lobTab
        If Not Application.Intersect(.ListColumns("Status Pgto").DataBodyRange, Target) Is Nothing Or _
           (Not Application.Intersect(.DataBodyRange, Target) Is Nothing And _
            Application.WorksheetFunction.CountBlank(.DataBodyRange.Rows(Target.Row - .HeaderRowRange.Row)) = 0) Then
        
            Formata_ColunaRegistro
        End If
    End With
End Sub

Wowww... parece que é isso, babdallas.

Mas estou tentando entender o código.

Contribuindo para meu aprendizado... poderia comentar essas linhas:

Código: Selecionar todos

If Not Application.Intersect(.ListColumns("Status Pgto").DataBodyRange, Target) Is Nothing Or _
           (Not Application.Intersect(.DataBodyRange, Target) Is Nothing And _
            Application.WorksheetFunction.CountBlank(.DataBodyRange.Rows(Target.Row - .HeaderRowRange.Row)) = 0) Then

Mais uma coisa... acrescentei à Macro "Formata_ColunaRegistro" as seguintes linhas:

Código: Selecionar todos

Sub Formata_ColunaRegistro()
    ...
    ...
    ...
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    ...
    ...
    ...
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    ...
    
End Sub

que melhoraram a performance da macro.
Porém ao sair de uma célula em que acabei de alterar um campo usando a tecla "TAB", por exemplo, ele executa a rotina e seleciona a coluna [Registro] inteira e eu saio da linha que foi alterada.
Gostaria que ele apenas fizesse a função de "TAB" mesmo e:
1) ou deslocasse a seleção para a célula da Tabela à direita daquela que foi alterada
2) ou que selecionasse a célula da coluna [Registro] da linha que foi alterada.
Como devo proceder?

E desde já muito obrigado.

Vamos lá: você deseja rodar a macro em 2 condições:
1) Se o status de pagamento foi modificado
ou
2)Se toda a linha onde algo foi modificado foi preenchida, desde que esta linha pertença aos dados da Tabela

A condição 1 é dada pelo código a seguir:

Código: Selecionar todos

Not Application.Intersect(.ListColumns("Status Pgto").DataBodyRange, Target) Is Nothing

em que:
.ListColumns("Status Pgto").DataBodyRange ==> significa todos os dados da coluna Status Pgto
Target ==> significa a célula que foi modificada, fazendo com que o evento Change fosse ativado

Sendo assim, a função Intersect verifica se, entre 2 ou mais Ranges (intervalos de células), há uma intersecção entre elas. Se eu verificar que o retorno é diferente de nada (not nothing), então eu garanto que a célula modificada estava na coluna de Status.
Ex para você entender melhor: Se a célula modificada foi a célula A5 e desejamos saber se a Célula A5 tem intersecção com a Coluna A, então a função Intersect irá verificar que há uma intersecção entre a célula A5 e a coluna A, retornando exatamente o intervalo de intersecção (que neste caso é A5). Se você procurar a intersecção entre a célula A5 e a coluna B, nunca haverá intersecção válida e a função Intersect irá retornar nada (nothing)

Já a condição 2 será dada por 2 condições:

1ª) A célula modificada (Target) seja da tabela TB_AtivDiarias, cujo código é

Código: Selecionar todos

Not Application.Intersect(.DataBodyRange, Target) Is Nothing

Neste código eu verifico se a célula modificada (Target) está dentor do intervalo de dados da tabela TB_AtivDiarias. Se for, a função Intersect irá retornar algo diferente de nada (not nothing).

2ª) Todas as células da linha da célula modificada (target) deverão estar preenchidas, ou seja, todas estas células deverão ter o CONTAR.VAZIO (Application.WorkshhetFunction.CountBlank()) igual a zero. O código abaixo faz esta verificação:

Código: Selecionar todos

Application.WorksheetFunction.CountBlank(.DataBodyRange.Rows(Target.Row - .HeaderRowRange.Row)) = 0

Ex: suponha que vc mexeu na célula A60 e o cabeçalho da Tabela está na linha 10. Logo, você mexeu na linha 50 da Tabela (60 - 10). Por isso eu acho a linha que foi alterada da Tabela com o seguinte código

Código: Selecionar todos

Target.Row - .HeaderRowRange.Row

Depois disso, em todas as linhas da tabela

Código: Selecionar todos

.DataBodyRange.Rows(Target.Row - .HeaderRowRange.Row)

eu deverei contar se o número de células vazias é zero, usando a função Application.WorksheetFunction.CountBlank

Não sei se fui claro em toda a explicação.

Não sei se fui claro em toda a explicação.

Perfeitamente claro na explicação.
Cada questão que me ajuda é uma aula aqui... muito obrigado.

Só reformulando aqui ... talvez não fui tão claro no que preciso.
A segunda condição está corretíssima funcionou perfeitamente...

Mas a outra condição não estaria restrita a Coluna [Status Pgto]... a usei apenas como exemplo.
A outra condição estaria ligada a ausência de cor de preenchimento.
Vamos usar como exemplo a linha com valor 75 na coluna Registro. Repare que a coluna [Plan Recibos] está como "Aguardando envio".
Quando a célula da Coluna [Plan Recibos] passa para o Status "Ok" ela "perde" a formatação condicional (cor amarela)... nessa condição seria desejado executar a Macro... quando a célula (e seria qualquer célula da linha, exceto a própria coluna[Registro]) "perdesse a formatação condicional.
Voltando ao exemplos:
Veja por exemplo a linha 73... ao passar o Status de [Plan Condomínio] para "Ok" ela deveria executar a macro "Formata_ColunaRegistro" pois a célula "perdeu" a formatação condicional.

Agora por exemplo a linha 75 ao passar o Status de [Plan Recibos] para "Ok" ela deveria executar a macro "Formata_ColunaRegistro" pois a célula "perdeu" a formatação condicional.
Nesse caso da linha 75, entretanto, a célula da coluna [Registro] deve manter a cor amarela por causa de regras de formatação próprias dessa coluna

Segue a planilha para verificação.

Porém ao sair de uma célula em que acabei de alterar um campo usando a tecla "TAB", por exemplo, ele executa a rotina e seleciona a coluna [Registro] inteira e eu saio da linha que foi alterada.
Gostaria que ele apenas fizesse a função de "TAB" mesmo e:
1) ou deslocasse a seleção para a célula da Tabela à direita daquela que foi alterada
2) ou que selecionasse a célula da coluna [Registro] da linha que foi alterada.
Como devo proceder?

Será que uma linha de comando do tipo:

Código: Selecionar todos

Target.Select

ou melhor ainda

Código: Selecionar todos

Target.Offset(0,1).select

seria o caminho para atender isso?

Código: Selecionar todos

Sub Formata_ColunaRegistro()
    Dim Plan As Worksheet
    Dim Tabela As ListObject
    Dim lngLin As Long, lngCol As Long, lngCor As Long
    Dim strRGB As String
    Dim ColZero As Long, ColReg As Long
    
    Set Plan = Wsh_AtivDiarias
    Set Tabela = Plan.ListObjects("TB_AtivDiarias")
    
    ColZero = Tabela.DataBodyRange.Range("A1").Column - 1
    ColReg = Tabela.ListColumns("Registro").DataBodyRange.Column - ColZero
    

    With Tabela.ListColumns("Registro").DataBodyRange.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Tabela.ListColumns("Registro").DataBodyRange.Font
        .ColorIndex = xlAutomatic
    End With
    
    With Tabela.DataBodyRange
        For lngLin = 1 To .Rows.Count
            lngCor = 0
            For lngCol = 1 To .Columns.Count
                strRGB = iColor(.Cells(lngLin, lngCol), "RGB")
                If strRGB <> "(255,255,255)" And strRGB <> "(217,225,242)" Then
                        lngCor = lngCor + 1
                End If
            Next lngCol
            If lngCor > 0 Then
                .Cells(lngLin, ColReg).Interior.Color = VBA.RGB(255, 235, 156)
                .Cells(lngLin, ColReg).Font.Color = VBA.RGB(156, 101, 0)
            End If
        Next lngLin
    End With
   
    Set Tabela = Nothing
    Set Plan = Nothing
End Sub

Veja se resolveu o problema do Tab.

Quanto ao problema da formatação condicional, ainda vou pensar a melhor maneira de fazer isso.

Olá...

Veja se resolveu o problema do Tab.

Essa parte resolveu perfeitamente

Quanto ao problema da formatação condicional, ainda vou pensar a melhor maneira de fazer isso.

Ok... fico no aguardo.

E desde já, obrigado.

No aguardo de ajuda.

Obrigado desde já.

Em relação à formatação condicional, a macro será rodada sempre que qualquer célula da tabela tenha sua formatação condicional modificada?

Bom dia.

Em relação à formatação condicional, a macro será rodada sempre que qualquer célula da tabela tenha sua formatação condicional modificada?

Analisando melhor o funcionamento da tabela aqui o ideal seria quando ela atendesse as duas condições:
1) Todas as células de uma determinada linha forem preenchidas
2) Quando a formatação condicional da célula de uma linha completamente preenchida for modificada.
Fico na dúvida se a própria coluna registro deveria ser incluída dessa regra.
Se pudesse ajudar na programação com as duas formas para que eu possa testar o funcionamento:
a) Incluindo a coluna [Registro] na regra
b) Excluindo a coluna [Registro] da regra.

Vejo 2 possibilidades de solução:

1) Verificar todas as regras de formatação condicional e colocar ela manualmente no código. Se houver alteração nas regras das formatações condicionais ou se houver exclusão ou adição de outras regras, o código também deverá ser modificado manualmente. Creio não ser uma solução boa.

2) Sempre que houver uma alteração na tabela, guardar em memória uma matriz com os códigos RGB das cores da célula. Quando uma nova célula for modificada, compararia a cor anterior (guardada em memória) coma cor atual. O problema disso é que se sua tabela for muito grande, o código vai ficar mais lento e a memória muito utilizada. Outra forma é guardar as cores RGB em uma planilha auxiliar ao invés de usar a memória.

Por enquanto não consigo pensar em mais alternativas para resolver isso. Veja o que você acha e qual solução deseja.

1) Verificar todas as regras de formatação condicional e colocar ela manualmente no código. Se houver alteração nas regras das formatações condicionais ou se houver exclusão ou adição de outras regras, o código também deverá ser modificado manualmente. Creio não ser uma solução boa.

Essa foi a que sempre utilizei... funciona.

A opção em mudar com a coluna [Registro] recebendo "formatação condicional" caso alguma outra célula recebesse formatação condicional foi justamente pq novas regras de formatação iam surgindo ou eram alteradas ou surgia uma nova coluna ou uma determinada coluna mudava de posição na tabela e daí as regras tinham que ser alteradas manualmente.

Cada coluna através de uma macro recebe sua regras próprias de formatação.... e para não repetir todas as regras para a coluna [Registro] (que é uma referência para apontar que existe uma situação de pendência naquela linha) tudo isso na coluna [Registro] surgiu a ideia de simplificar com a Macro "Formata_Registro" que colore a Coluna [Registro] caso alguma célula da linha receba formatação condicional.

Resumindo... se alguma célula da linha receber formatação condicional ela "colore" a coluna [Registro] e não precisaria repetir as regras para essa coluna.

2) Sempre que houver uma alteração na tabela, guardar em memória uma matriz com os códigos RGB das cores da célula. Quando uma nova célula for modificada, compararia a cor anterior (guardada em memória) coma cor atual. O problema disso é que se sua tabela for muito grande, o código vai ficar mais lento e a memória muito utilizada. Outra forma é guardar as cores RGB em uma planilha auxiliar ao invés de usar a memória.

Gostaria de testar essa alternativa de gerar a matriz e avaliar se ficaria viável.
A segunda opção dessa possibilidade (com planilha auxiliar) confesso que não sei como seria mas posso testar também se puder me ajudar com isso.

Boa tarde, babdallas.

Se tiver aquela opção disponível gostaria de testar a implementação.

Veja se atende.

Boa noite.

Show demais, babdallas!

Achei muito boa essa macro... vou avaliar com o tempo como fica o desempenho dela conforme a tabela aumenta o número de dados.

Mas por enquanto ficou fantástica.

Muito obrigado.

Guru do Excel e Power BI

Rodar macro ao atender condições

Rodar macro ao atender condições

Re: Rodar macro ao atender condições

Re: Rodar macro ao atender condições

Re: Rodar macro ao atender condições

Re: Rodar macro ao atender condições

Re: Rodar macro ao atender condições

Re: Rodar macro ao atender condições

Re: Rodar macro ao atender condições

Re: Rodar macro ao atender condições

Re: Rodar macro ao atender condições

Re: Rodar macro ao atender condições

Re: Rodar macro ao atender condições

Re: Rodar macro ao atender condições

Re: Rodar macro ao atender condições

Re: Rodar macro ao atender condições

Re: Rodar macro ao atender condições