Contar Células Coloridas pela Formatação Condicional

Olá pessoal.
Eu já havia escrito um artigo semelhante ao que vocês lerão agora, contudo, muitas pessoas vêm me pedindo uma melhoria nele para atender às células que tem seus backgrounds definidos dinamicamente via Formatação condicional.
Então. Hoje vou mostrar como fazer isso. Para quem não leu a matéria anterior sobre este assunto, leia para entender melhor este artigo.
Vou transcrever aqui, o comentário do leitor Alex que solicitou esta dica recentemente no dia 26 de junho de 2010.

Muito boa essa ajuda. Contudo se a celula por formatada condicionalmente esse aplicativo não funcional. Ex.: Formato a celula condicionalmente para amarelo se o valor for igual a 5. Coloco esse valor em na celula e ela muda de cor automatica pelo sistema. Só que essa formula nao reconhece a celular como amarelo. Apenas como branca. Mesmo ela estando amarela. Tem como resolver isso?

Bem, caros leitores, existe sim a possibilidade de fazer uma contagem de células que são “coloridas” pela formatação condicional. Seguindo a mesma metodologia da versão desenvolvida anteriormente, essa fórmula será chamada através da seguinte fórmula personalizada, digitada diretamente na célula onde se quer exibir a contagem.
Exemplo:
=ContaCelulaColoridaFormatCond(D6;$A$2:$A$30)
Onde em D6 está uma célula “colorida” com a cor que deseja exibir a contagem.
No final deste artigo, está disponível um arquivo de exemplo para você baixar e entender melhor o funcionamento.
Abaixo, vou descrever o código que executará toda esta tarefa. Lembre-se, este código deverá ser colocado em um módulo global no ambiente de desenvolvimento VBA. Para abrir o editor VBA, tecle ALT+F11.
No módulo.

Option Explicit
Public Function ContaCelulaColoridaFormatCond(rngColorInfo As Range, Intervalo As Range) As Long
Dim rConta As Range
For Each rConta In Intervalo.Cells
If RetornaCorDeFundoCondicional(rConta) = rngColorInfo.Interior.ColorIndex Then
ContaCelulaColoridaFormatCond = ContaCelulaColoridaFormatCond + 1
End If
Next
End Function
Public Function RetornaCorDeFundoCondicional(ByVal rngCelula As Range) As Long
Dim FormatCondition As FormatCondition
RetornaCorDeFundoCondicional = -1
For Each FormatCondition In rngCelula.FormatConditions
If StatusDoFormatoCondicional(FormatCondition) Then
If Not IsNull(FormatCondition.Interior.ColorIndex) Then
RetornaCorDeFundoCondicional = FormatCondition.Interior.ColorIndex
End If
Exit For
End If
Next FormatCondition
End Function
Public Function StatusDoFormatoCondicional(ByVal FormatCondition As FormatCondition) As Boolean
Dim FormulaTransformada As String
Dim Operator As Long
Dim Formula1 As String
Dim Formula2 As String
Dim Cell As Range
Dim CellValue As String
Application.Volatile
FormulaTransformada = FormatCondition.Formula1
Set Cell = FormatCondition.Parent
On Error Resume Next
Operator = FormatCondition.Operator
On Error GoTo 0
If Operator > 0 Then
Formula1 = FormatCondition.Formula1
On Error Resume Next
If Left(Formula1, 1) = "=" Then Formula1 = Mid(Formula1, 2)
Formula2 = FormatCondition.Formula2
On Error GoTo 0
If Left(Formula2, 1) = "=" Then Formula2 = Mid(Formula2, 2)
If VarType(Cell.Value) = vbString Then
CellValue = """" & Cell.Value & """"
Else
CellValue = CDbl(Cell.Value)
End If
Select Case Operator
Case xlBetween: FormulaTransformada = "AND(" & Formula1 & "<=" & CellValue & "," & CellValue & "<=" & Formula2 & ")"
Case xlNotBetween: FormulaTransformada = "OR(" & Formula1 & ">" & CellValue & "," & CellValue & ">" & Formula2 & ")"
Case xlEqual: FormulaTransformada = CellValue & "=" & Formula1
Case xlNotEqual: FormulaTransformada = CellValue & "<>" & Formula1
Case xlGreater: FormulaTransformada = CellValue & ">" & Formula1
Case xlLess: FormulaTransformada = CellValue & "<" & Formula1
Case xlGreaterEqual: FormulaTransformada = CellValue & ">=" & Formula1
Case xlLessEqual: FormulaTransformada = CellValue & "<=" & Formula1
End Select
Else
'Caso a formatação condicional seja uma fórmula
FormulaTransformada = FormatCondition.Formula1
FormulaTransformada = Replace(FormulaTransformada, ";", ",")
'Traduzindo a função SE para o inglês
FormulaTransformada = Replace(FormulaTransformada, "SE(", "IF(")
'Adicione traduções para as funções que você usar
'Exemplos:
'FormulaTransformada = Replace(FormulaTransformada, "MÉDIA(", "AVG(")
'FormulaTransformada = Replace(FormulaTransformada, "SOMA(", "SUM(")
'FormulaTransformada = Replace(FormulaTransformada, "SOMASE(", "SUMIF(")
'FormulaTransformada = Application.ConvertFormula(FormulaTransformada, xlA1, xlR1C1, , ActiveCell)
'FormulaTransformada = Application.ConvertFormula(FormulaTransformada, xlR1C1, xlA1, xlAbsolute, Cell)
FormulaTransformada = Application.ConvertFormula(FormulaTransformada, xlA1, xlR1C1, xlRelative, FormatCondition.AppliesTo.Resize(1, 1))
FormulaTransformada = Application.ConvertFormula(FormulaTransformada, xlR1C1, xlA1, xlRelative, Cell)
End If
StatusDoFormatoCondicional = Application.Evaluate(FormulaTransformada)
End Function

É isso!
Espero que tenham gostado. Abraços.
 
UPDATE (Dez/2012):  Código atualizado para contemplar as condições que utilizam fórmulas com funções do excel. Disponível o arquivo atualizado para download.
UPDATE (Abr/2013):  Código atualizado para contemplar as condições que utilizam fórmulas com funções do excel. Disponível o arquivo atualizado para download.

56 comentários em “Contar Células Coloridas pela Formatação Condicional”

  1. Excelente solução. Pena que o excel não as traga prontas.
    Reinaldo apliquei sua FÓRMULA e deu certo, porém não atualiza a medida que a formatação condicional é alterada. É necessário abrir e fechar a planilha novamente ou clicar e arrastar…mas tudo bem.
    Gostaria de ter o mesmo recurso para tratar “FONTES” coloridas.
    Você já tratou este assunto? Se não, é possível faze-lo?
    Aguardo retorno.
    Sds,
    Max.

    Responder
  2. Muito boa sua fórmula.
    Gostaria de saber o que preciso mudar nesta fórmula para somar os valores que estão nas celulas coloridas pela formatação condicional?
    Valeu!

    Responder
  3. Olá, estava procurando justamente isto, uma planilha que contasse células coloridas na forma condicional.
    A Fórmula funciona direitinho quando as células estão na vertical A2:A30, mas quando estão na horizontal (A2:P2), está dando um erro. Tentei tanto na minha planilha, que fiz usando o teu VBA, como na tua planilha, condicionando algumas células horizontalmente.
    O que precisa alterar para funcionar?
    Obrigado!

    Responder
  4. está retornando o seguinte:
    Case xlBetween: FormulaTransformada = “AND(” & Formula1 & “<=" & CellValue & "," & CellValue & "” & CellValue & “,” & CellValue & “>” & Formula2 & “)”
    como poderia me ajudar?
    obrigado

    Responder
  5. Meu caro Reinaldo, eu gostaria na minha planilha contar os NOMES que estão em Células Coloridas pela Formatação Condicional e minha planilha está no formato D5:D30 dessa coluna até a coluna AH5:AH30, pois sua planilha está protegida e como que irei fazer as alterações e se possível você me enviar se preferir pronta ai eu só altero aonde você me falar para colocar os nomes que vão nessas planilha se for o caso. Fico grato.

    Responder
  6. Meu caro Reinaldo, eu gostaria que na minha planilha em vez de números contar NOMES que estão em Células Coloridas pela Formatação Condicional e minha planilha está no formato D5:D30 (D5 = Augusto; D6 = Beto ; D7 = Tiao …..) dessa coluna até a coluna AH5:AH30, pois sua planilha está protegida? Pois não está aparecendo no VBAProject para mim o MÓDULO deve está oculto ou protegido para não aparecer coisa desse tipo, para fazer alteração e também gostaria que caso na minha planilha houvesse uma alteração mudasse automaticamente com aquela atualização. Gostaria se possível você me enviar por e-mail se preferir ela pronta ai eu só altero aonde você me falar para colocar os nomes que vão nessas planilha se for o caso. Fico grato.

    Responder
  7. Boa noite, eu gostaria de adaptar para minha planilha, mas não sei por onde começar, será que pode me dar um HELP.
    É o seguinte tenho uma planilha contendo vários nomes (se quiser eu te envio eh soh passar o e-mail) em vez de número dentro dessas células C4:AG29 correto, existem alguns nomes que estao com cores (amarela e/ou verde e/ou vermelho) em células diferentes mas a fonte/letra permanece todas com cor normal em preto. Portanto eu gostaria de contar quantas vezes por exemplo o nome de TIAO aparece na planilha com as células preenchida tanto nas cores por (verde/amarelo e vermelho)e assim para os outros nomes também. Mas isso sem SER NA FORMATAÇÃO CONDICIONAL. Fico grato.

    Responder
  8. Boa tarde,
    Baixei o arquivo e copiei a fórmula, mas não sei por que não da certo.
    O Excel não diz nada. Apenas quando clico em “Fórmulas / Verificação de Erros” diz que “Um valor usado na fórmula tem o tipo de dados incorreto”.
    O que posso fazer? Sabe o que pode estar acontecendo?
    Existe algum e-mail para o qual posso enviar minha planilha para que você de uma olhada, talvez ajude outras pessoas.
    Obrigado!
    Abraços,

    Responder
  9. Boa noite eu novamente,
    Alterei: ContaCelulaColorida = ContaCelulaColorida + 1
    Por: ContaCelulaColorida = ContaCelulaColorida + rConta.Value
    e alterei: Function ContaCelulaColorida(rngColorInfo As Range, Intervalo As Range) As Long
    por: Function ContaCelulaColorida(rngColorInfo As Range, Intervalo As Range) As Double
    Modifiquei também: CellValue = CDbl(Cell.Value)
    para: CellValue = Replace(Cell.Value, “,”, “.”)
    Mas quando tento fechar o VB aparece uma janela dizendo “Erro de compilação: Variavel não definida”
    Poderia me ajudar, por favor?
    Obrigado!

    Responder
  10. Olá, inseri o código copiado do arquivo em anexo no VB do meu arquivo, inseri a fórmula e não funcionou. Essa ferramenta me seria muito útil, o que devo fazer para que funcione, alguém poderia montar um passo a passo de como fazer isso funcionar no arquivo do excel. Uso o Excel a pouco tempo, derrepente eu não esteja fazendo isso corretamente. Agradeço pela atenção.

    Responder
  11. Reinaldo Coral, boa noite.
    Fiz como vc descreve a cima e tenho a resposta de ERRO de COMPILAÇÃO / ERROS DE SINTAXE na linha 58. Ai ficam algumas dúvidas:
    1º ao colocar a programação que vc fez em inserir modulo a programação deve ir com a numeroção que esta junto ou devo apaga-lo?
    2º após feito isso deve-se fechar o arquivo microsoft visual basic?
    Tenho um exemplo do que necessito em PDF e se possivel gostaria de posta-lo ou envia0lo a vc para que pudesse avalia-lo e mostrar qual q melhor formula a usar para contar celulas coloridas.
    Contando com sua colaboração e paciência.
    Agradeço desde já. Cordialemnte.
    Jefferson

    Responder
    • Bom dia,
      A numeração do código não deve ser escrita não.
      Sugestão: No artigo aqui no site, quando você passar o mouse sobre o código, vai aparecer no topo do código, alguns ícones, clique no primeiro ícone para visualizar o código em texto puro, ou no segundo ícone para copiar o código.
      Desta maneira, a numeração e os caracteres HTML não serão copiados, ok.
      Quanto a fechar a janela do VBA não se faz necessário. A compilação é imediata.
      Abraço.

      Responder
  12. 2 3 4 6 12 14 15 17 18 19 20 21 22 24 25 Soma cel. Col.
    1 3 5 6 7 8 9 10 14 16 17 18 20 21 22 24 9
    2 4 5 6 7 8 9 10 15 16 17 18 20 23 24 25 8
    3 3 4 5 6 7 8 10 12 14 15 16 21 23 24 25 9
    4 3 4 5 6 7 8 10 12 14 15 18 21 23 24 25 10
    5 4 5 6 7 8 9 10 12 15 16 18 22 23 24 25 8
    6 3 4 7 9 12 14 15 16 17 18 20 21 22 23 25 11
    7 3 4 5 8 9 10 12 14 15 17 20 21 22 23 25 10
    8 3 4 6 8 9 12 14 15 17 20 21 22 23 24 25 12
    9 1 2 3 5 6 7 8 10 11 13 14 16 18 19 20
    10 1 2 4 8 9 11 12 13 16 17 19 21 22 24 25
    11 1 2 3 7 10 11 13 15 17 18 19 21 22 23 24
    12 1 2 4 5 6 9 11 12 13 14 15 19 20 23 25
    13 1 2 3 4 7 8 9 10 11 12 13 15 18 19 25
    14 1 2 5 6 8 11 13 14 15 17 19 20 21 22 24
    15 1 2 5 7 11 13 15 16 17 18 19 20 21 22 24
    16 1 2 3 4 6 10 11 12 13 14 16 17 19 21 23
    17 1 2 4 6 7 8 11 12 13 14 18 19 22 23 24
    18 1 2 3 5 8 9 10 11 13 17 19 20 21 23 25
    19 1 2 4 5 7 11 12 13 16 17 18 19 20 21 23
    20 1 2 3 6 9 10 11 13 14 15 16 19 22 24 25
    21 1 2 6 7 8 9 11 13 14 15 17 18 19 21 25
    22 1 2 3 4 5 8 10 11 12 13 15 19 20 22 24
    23 1 2 3 5 7 8 10 11 13 15 16 18 19 20 23
    Em uma planilha do excel tenho uma tabela com 25 linhas e com 16 colunas. tenho numeros como referencia (linha 1) e estou usando formatação condicional para colorir as celulas que são iguais aos numeros de referencia. Necessito somar (por linha) a quantidade de celulas coloridas, conforme exemplo abaixo. linha 2 = 8 celulas coloridas linha 3 = 9 linhas coloridas e assim por diante.
    linha 1- _ 1 2 4 6 8 10 11 12 14 16 17 18 19 21 22 <=== num. de referencia.
    linha 2- 1 1 2 3 5 6 8 11 13 14 16 17 20 23 25 28 8 celulas
    linha 3- 2 1 2 4 6 8 9 11 13 15 16 17 18 20 23 25 9 celulas
    linha 4- 3 1 3 4 5 9 10 15 16 20 21 22 23 24 25 26 6 celulas
    linha 5- 4 3 6 8 10 11 12 13 14 15 16 17 18 19 21 22 12 celulas
    linha 6- 5
    linha 7- 6
    linha 8- 7
    linha 9- 8
    Na segunda linha teremos 08 celulas coloridas
    terceira linha teremos 09 celulas coloridas
    quarta linha teremos 06 celulas coloridas
    quinta linha teremos 12 celulas coloridas
    Grato pela atenção dada ao meu problema.
    Jefferson.

    Responder
    • Boa tarde Janaina.
      Tente baixar o arquivo fonte que disponibilizei no artigo e faça seu estudo a partir dele. Geralmente, quando o usuário copia o código direto do site e cola no VBA é carregado parte das codificações html e gera erro.
      Abç

      Responder
  13. Executei os comandos,
    No caso do primeiro exemplo onde faz o calculo com células coloridas comuns funcionou perfeitamente,
    Já com a formação de condicional não há nenhum resultado,
    Executei o mesmo processo de montar as tabelas para cálculo e não funcionou, existe algum modo diferente de executar a fórmula?
    Obrigada desde já.

    Responder
  14. Olá
    Obrigado pelas dicas!
    Peguei suas dicas e fiz o teste e funcionou, porém com a formatação condicional não funciona quando a celulá contém uma fórmula criada pelo usuário via vba.
    ex: criei uma formula para fixar a o horário da forlula =agora(), planilha estilo marcar ponto
    se A1= “” B1=formula que criei e vba valida a formula para fixar o valor da hora, pois a formula =agora() sozinha muda o horário sempre que a planilha é atualizada.
    Então usei a formatação condicional usando data e hora e funciona beleza, mas não conta usando seu código, fiz o teste sem minha formula e funciona, e com formulas do excel tambem.
    Por gentileza, pode me dar uma luz, posso lhe enviar a planilha.
    Grato
    MARCOS

    Responder
  15. Desculpa, mas baixei o arquivo original e ele também apresenta erro. Quando faço um arquivo meu, ele executar normalmente, porém se eu o fecho e abro novamente, a fórmula existe, mas os cálculos apresentam erro. Poderiam em ajudar

    Responder
    • Bruna, realmente não dá para saber o que está ocorrendo aí.
      Em todos os testes aqui, nas diversas versões do Excel, funciona muito bem.
      Uma idéia: Você já testou em outro computador? Pode ser algum problema na instalação do Excel.
      Abç

      Responder
  16. Buenas.
    Baixei o seu exemplo e funcionou sem qualquer problema, mas ao adaptar ao que eu preciso a formula não funciona.
    Eu tenho um relatorio em que existem colunas alternadas em que a celula só mudad de cor caso duas ~condições sejam satesfeitas. esse problema foi resolvido, o outro problema é que ao lançar a formula do exemplo na minha planilha ele funciona somente na primeira celula, quando espando para a proxima celula a formaula retorna erro.
    Pude verificar a formula funciona em celulas seguidas, mas meu caso são alternadas.
    Teria como me dar uma ajuda no meu problema?
    Caso necessário posso enviar a planilha exemplo por e-mail.
    Se sim me inform.
    Preciso muito de ajuda, pois é um relatório que faço para a diretoria da empresa e é diário. Todos os dias tenho que alimentar o arquivo e depois ficar vendo quais as informações que se repetem e sinalizar na mão, com essa formula seria uma mão na roda.
    Desde já agradeço.
    Abraços

    Responder
      • Buenas Reinaldo!
        Conforme orientado enviei do meu e-mail (henry-systems@….com) para o endereço indicado o arquivo compactado (contagem_celulas_coloridas_format_condic.rar) para sua análise.
        Desde já agradeço por sua atenção e ajuda.
        Att

        Responder
        • Sim Henry-Systems. Já enviei uma solução pro teu email.
          Vou explicar para os demais leitores.
          Havia um bug ao utilizar esta macro com fórmulas que possuem funções. As fórmulas precisam estar em inglês. Como a macro foi feita com base numa versão do Excel em inglês, o resultado era positivo.
          Para resolver isto, fiz algumas alterações no código, que converterão as funções para inglês, possibilitando a execução das mesmas. Contudo, há um porém.
          Será necessário incluir uma linha de comando para cada função que você utilizar em suas formatações condicionais. No exemplo que o Henry me enviou havia apenas uma fórmula com a função SE.
          O comando para a tradução seria este:
          FormulaTransformada = Replace(FormulaTransformada, “SE(“, “IF(“)
          Dessa forma, trocaremos o SE por IF.
          Se fosse uma SOMA por exemplo, ficaria assim:
          FormulaTransformada = Replace(FormulaTransformada, “SOMA(“, “SUM(“)
          Entenderam?
          É claro que fiz algumas outras pequenas alterações. Em breve postarei as atualizações no site, ok.
          Abraços

          Responder
        • Buenas Reinaldo.
          Desculpe a demora em responder, mas o tempo por aqui está corrido.
          Consegui resolver o problema.
          NA verdade acabei por utilizar o exempo do topico “https://www.exceldoseujeito.com.br/2008/11/21/contar-celulas-coloridas/” em conjunto com a formatação condicional, com essa maneira a condicional marca as duplicidades na co0r que defeni, aí em filtro e pinto as celulas da cor que está defenidada na formula da macro e pronto, problema resolvido.
          Caso queira o exemplo posso mandar por e-mail para que possa disponibilizar para quem queira utilizar a ideia.
          Desde já agradeço pela ajuda.
          Abraços

          Responder
  17. Gostei da explicação, porem não tenho prática com VBA e se possível mostrar como eu faço para que o módulo fique de forma global pois tive alguns problemas.

    Responder
  18. Bom dia Reinaldo, baixei sua aplicação e roda certinho… tentei adaptar para a minha planilha mais ela retorna erro #valor.
    Eu tenho uma planilha com notas de alunos… e eu queria somar automaticamente as celulas condicionas em vermelho. Já coloquei o codigo no modullo global mais fica retornando esse erro…. alguma dica do que pdoe estar ocorrendo?
    obrigado

    Responder
  19. Ola Reinaldo, ficou muito boa a explicação e os testes funcionaram perfeitamente. No entanto, se o fundo de uma célula depende de outra, retorna o #VALOR!
    Por exemplo, se o fundo de A1 for uma formatação condicional do resultado de A1 e B1 ele não entende… bem “eu” não entendo 🙂
    Seria possível me ajudar com esse problema? No caso, quero comparar dois ou mais valores e se encontrar valores iguais gostaria de contabilizar os encontrados.
    Exemplo: Buscar valores repetidos entre duas colunas “A e B” e se encontrar por exemplo dois nomes iguais somassem somente o de “A”.
    Deixar os nomes iguais destacados foi fácil usando a formatação “básica” condicional (Valores Duplicados), mas para contabilizar não estou conseguindo.
    Agradeço antecipadamente,

    Responder
  20. Olá,
    no meu caso a macro só funciona quando eu coloco formatações condicionais simples, por exemplo Valor da Célula é maior que =$E$6. porém quando eu coloco a formatação esta entre 2 células, a macro retorna #VALOR!
    Att,
    Edgar Gionedis

    Responder

Deixe um comentário