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
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 = Replace(Cell.Value,",",".") 'CDbl(Cell.Value) '*** Modificado em 10-jan-2010
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
FormulaTransformada = FormatCondition.Formula1
If Application.Version < 12 Then
If Application.ReferenceStyle = xlA1 Then
FormulaTransformada = Application.ConvertFormula(FormulaTransformada, xlA1, xlR1C1, , ActiveCell)
FormulaTransformada = Application.ConvertFormula(FormulaTransformada, xlR1C1, xlA1, xlAbsolute, Cell)
End If
End If
End If
StatusDoFormatoCondicional = Application.Evaluate(FormulaTransformada)
End Function
É isso!
Espero que tenham gostado. Abraços.
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.
Reply
Reinaldo Coral Reply:
setembro 14th, 2010 at 18:12
Obrigado.
Quanto a adaptação da macro para “fontes”, basta alterar as referências a cor de fundo (Interior.ColorIndex) por Font.ColorIndex.
Abrç.
Reply
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!
Reply
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!
Reply
Reinaldo Coral Reply:
outubro 12th, 2010 at 22:08
Caríssimo, esta fórmula funciona tanto para intervalos verticais quanto para horizontais, ou ambos.
Não sei o erro que está aparecendo para você, mas, no meu teste aqui, funcionou perfeitamente.
Abçs
Reply
a fórmula não funciona com resultados decimais. Como adaptar o código?
Reply
Reinaldo Coral Reply:
janeiro 10th, 2011 at 16:18
Oi Lauren,
Localize no código a linha:
CellValue = CDbl(Cell.Value)
e altere para:
CellValue = Replace(Cell.Value, “,”, “.”)
Abç.
Reply
Felipe Borges Reply:
fevereiro 14th, 2011 at 7:22
No meu caso essa solução nao funcionou nao sei pq. A formula funciona perfeitamente utilizando números inteiros, porém meu intervalo de valores validos esta entre 57,015 a 57,85 e ao usar números decimais a celula da formula apresenta:
#VALOR!
Aguardo ajuda…
Muito boa a explicação de td…vlw…
ABRACO
Reply
Reinaldo Coral Reply:
fevereiro 14th, 2011 at 14:36
Leia o comentário:
http://www.exceldoseujeito.com.br/2010/07/03/contar-celulas-coloridas-pela-formatacao-condicional/comment-page-1/#comment-1939
Já tinha resolvido isto, porém, não tinha atualizado no artigo. agora está ok.
Abç
—————————————————
Siga-nos no twitter: @exceldoseujeito
está retornando o seguinte:
Case xlBetween: FormulaTransformada = “AND(” & Formula1 & “<=" & CellValue & "," & CellValue & "” & CellValue & “,” & CellValue & “>” & Formula2 & “)”
como poderia me ajudar?
obrigado
Reply
Sérgio Reply:
janeiro 29th, 2011 at 22:50
ja arrumei agora retorna outro erro:
Public Function ContaCelulaColoridaFormatCond(rngColorInfo As Range, Intervalo As Range) As Long
=> erro de compilação
Reply
Sérgio Reply:
janeiro 29th, 2011 at 23:06
Nome repetido encontrado: ContaCelulaColoridaFormatCond
Reply
Sérgio Reply:
janeiro 30th, 2011 at 18:09
ehehe meu problema agora é outro, dependendo do intervalo que eu escolho ele funciona direito. se eu pego um intervalo grande ele retorna error de VALOR.
Reinaldo Coral Reply:
janeiro 31st, 2011 at 13:08
Sérgio,
Você baixou o arquivo fonte de exemplo?
No arquivo original do exemplo não está apresentando este erro.
Talvez seja alguma modificação que tenha feito no código.
Abç
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,
Reply
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!
Reply
Reinaldo Coral Reply:
outubro 20th, 2011 at 10:25
O problema pode ser com as aspas duplas…. se copiar do site e colar dá esse problema… (elas vão como símbolo html e o compilador não entende!)
Redigite as aspas no código, vai resolver.
Abç
Reply
Ops!
onde se lê: ContaCelulaColorida
considere: ContaCelulaColoridaFormatCond
Reply
Se tiver feito um primeiro modulo, com a configuração para contagem de celulas coloridas, este funciona? ou devo substituir?
Está dando algum problema…
Reply
Reinaldo Coral Reply:
janeiro 26th, 2012 at 15:11
As duas funções funcionam em harmonia, sem incompatibilidade.
Não é necessário substituir o módulo. Apenas adicione os novos códigos.
Abç
Reply
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
Reply
Reinaldo Coral Reply:
fevereiro 4th, 2012 at 9:50
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.
Reply