Uso da Formatação Condicional com o Excel VBA

Formatação Condicional do Excel

A formatação Condicional do Excel permite que você defina regras que determinam a formatação das células.

Por exemplo, você pode criar uma regra que destaque as células que atendam a determinados critérios. Os exemplos incluem:

• Números que se enquadram em um determinado intervalo (por exemplo, menor que 0).
• Os 10 principais itens de uma lista.
• Criação de um “mapa de calor”.
• regras “baseadas em fórmulas” para praticamente qualquer formatação condicional.

No Excel, a formatação condicional pode ser encontrada na faixa de opções em Página Inicial > Estilos (ALT > C > LR).

Para criar sua própria regra, clique em “Nova regra” e uma nova janela será exibida:

Formatação Condicional em VBA

Todos esses recursos de formatação condicional podem ser acessados usando o VBA.

Observe que, ao configurar a formatação condicional a partir do código VBA, os novos parâmetros aparecerão na janela de formatação condicional front-end do Excel e ficarão visíveis para o usuário. O usuário poderá editar ou excluir esses parâmetros, a menos que você tenha bloqueado a planilha.

As regras de formatação condicional também são salvas quando a planilha é salva

As regras de formatação condicional se aplicam especificamente a uma determinada planilha e a um determinado intervalo de células. Se forem necessárias em outro local da pasta de trabalho, elas também deverão ser configuradas nessa planilha.

Usos Práticos da Formatação Condicional no VBA

Você pode ter uma grande quantidade de dados brutos importados para a planilha a partir de um arquivo CSV (valores separados por vírgula) ou de uma tabela ou consulta de banco de dados. Estes dados podem migrar para um painel ou relatório, com números variáveis importados de um período para outro.

Quando um número muda e está fora de um intervalo aceitável, talvez você queira destacar isso, por exemplo, com a cor de fundo da célula em vermelho, o que pode ser feito configurando a formatação condicional. Dessa forma, o usuário será instantaneamente atraído para esse número e poderá investigar por que isso está acontecendo.

Você pode usar o VBA para ativar ou desativar a formatação condicional. Você pode usar o VBA para limpar as regras em um intervalo de células ou ativá-las novamente. Pode haver uma situação em que exista um motivo perfeitamente válido para um número incomum, mas quando o usuário apresentar o painel ou o relatório a um nível mais alto de gerência, ele desejará poder remover os “sinais de alarme”.

Além disso, nos dados brutos importados, talvez você queira destacar os números que são ridiculamente grandes ou ridiculamente pequenos. O intervalo de dados importados geralmente tem um tamanho diferente para cada período, portanto, você pode usar o VBA para avaliar o tamanho do novo intervalo de dados e inserir a formatação condicional somente para esse intervalo.

Você também pode ter uma situação em que há uma lista ordenada de nomes com valores numéricos em cada um deles, por exemplo, salário do funcionário, notas de exames. Com a formatação condicional, você pode usar cores graduadas para ir do maior para o menor valor, o que é muito impressionante para fins de apresentação.

Entretanto, a lista de nomes nem sempre terá um tamanho estático, e você pode usar o código VBA para atualizar a escala de cores graduadas de acordo com as alterações no tamanho do intervalo.

Um Exemplo Simples de Criação de um Formato Condicional em um Intervalo

Este exemplo configura a formatação condicional para um intervalo de células (A1:A10) em uma planilha. Se o número no intervalo estiver entre 100 e 150, a cor de fundo da célula será vermelha; caso contrário, não terá cor.

Sub ExemploFormatacaoCondicional()

'Definir Intervalo
Dim MeuIntervalo As Range
Set MeuIntervalo = Range("A1:A10")


'Apagar Formatação Condicional Existente no Intervalo
MeuIntervalo.FormatConditions.Delete


'Aplicar Formatação Condicional
MeuIntervalo.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=100", Formula2:="=150"
MeuIntervalo.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End Sub

Observe que primeiro definimos o intervalo MeuIntervalo para aplicar a formatação condicional.

Em seguida, excluímos qualquer formatação condicional existente para o intervalo. Essa é uma boa ideia para evitar que a mesma regra seja adicionada toda vez que o código for executado (é claro que isso não será apropriado em todas as circunstâncias).

As cores são dadas por valores numéricos. É uma boa ideia usar a notação RGB (Red, Green, Blue) para isso. É possível usar constantes de cores padrão para isso, por exemplo, vbRed, vbBlue, mas você está limitado a oito opções de cores.

Há mais de 16,7 milhões de cores disponíveis e, usando RGB, você pode acessar todas elas. Isso é muito mais fácil do que tentar lembrar qual número corresponde a qual cor. Cada um dos três números de cores RGB vai de 0 a 255.

Observe que o parâmetro ‘xlBetween’ é inclusivo, portanto, valores de célula de 100 ou 150 satisfarão a condição.

Formatação Multicondicional

Talvez você queira configurar várias regras condicionais em seu intervalo de dados para que todos os valores em um intervalo sejam cobertos por condições diferentes:

Sub ExemploFormatacaoMultiCondicional()
Dim MeuIntervalo As Range
'Criar Objeto Range
Set MeuIntervalo = Range("A1:A10")
'Apagar formatação condicional anterior
MeuIntervalo.FormatConditions.Delete
'Adicionar Primeira Regra
MeuIntervalo.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=100", Formula2:="=150"
MeuIntervalo.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
'Adicionar Segunda Regra
MeuIntervalo.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=100"
MeuIntervalo.FormatConditions(2).Interior.Color = vbBlue
'Adicionar Terceira Regra
MeuIntervalo.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=150"
MeuIntervalo.FormatConditions(3).Interior.Color = vbYellow
End Sub

Este exemplo configura a primeira regra como anteriormente, com a cor vermelha da célula se o valor da célula estiver entre 100 e 150.

Em seguida, são adicionadas mais duas regras. Se o valor da célula for menor que 100, a cor da célula será azul e, se for maior que 150, a cor da célula será amarela.

Neste exemplo, você precisa garantir que todas as possibilidades de números sejam cobertas e que as regras não se sobreponham.

Se houver células em branco nesse intervalo, elas serão exibidas em azul, porque o Excel ainda as considera como tendo um valor menor que 100.

A maneira de contornar isso é adicionar outra condição como uma expressão. Isso precisa ser adicionado como a primeira regra de condição no código. É muito importante que, quando houver várias regras, a ordem de execução esteja correta, caso contrário, os resultados poderão ser imprevisíveis.

Sub ExemploFormatacaoMultiCondicional()
Dim MeuIntervalo As Range
'Criar Objeto Range
Set MeuIntervalo = Range("A1:A10")
'Apagar formatação condicional anterior
MeuIntervalo.FormatConditions.Delete
'Adicionar Primeira Regra
MeuIntervalo.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=NÚM.CARACT(ARRUMAR(A1))=0"
MeuIntervalo.FormatConditions(1).Interior.Pattern = xlNone
'Adicionar Segunda Regra
MeuIntervalo.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=100", Formula2:="=150"
MeuIntervalo.FormatConditions(2).Interior.Color = RGB(255, 0, 0)
'Adicionar Terceira Regra
MeuIntervalo.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=100"
MeuIntervalo.FormatConditions(3).Interior.Color = vbBlue
'Adicionar Quarta Regra
MeuIntervalo.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=150"
MeuIntervalo.FormatConditions(4).Interior.Color = RGB(0, 255, 0)
End Sub

Isso usa o tipo de xlExpression e, em seguida, usa uma fórmula padrão do Excel para determinar se uma célula está em branco em vez de um valor numérico.

O objeto FormatConditions faz parte do objeto Range. Ele funciona da mesma forma que uma coleção, com o índice começando em 1. Você pode iterar por esse objeto usando um loop For…Next ou For…Each.

Exclusão de uma Regra

Às vezes, pode ser necessário excluir uma regra individual em um conjunto de várias regras se ela não se adequar aos requisitos dos dados.

Sub ExemploApagarFormatacaoCondicional()
Dim MeuIntervalo As Range
'Criar Objeto Range
Set MeuIntervalo = Range("A1:A10")
'Apagar formatação condicional anterior
MeuIntervalo.FormatConditions.Delete
'Adicionar Primeira Regra
MeuIntervalo.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=100", Formula2:="=150"
        MeuIntervalo.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
'Apagar Regra
MeuIntervalo.FormatConditions(1).Delete
End Sub

Esse código cria uma nova regra para o intervalo A1:A10 e, em seguida, a exclui. Você deve usar o número de índice correto para a exclusão, portanto, verifique em “Gerenciar regras” no front-end do Excel (isso mostrará as regras em ordem de execução) para garantir que você obtenha o número de índice correto. Observe que não há recurso de desfazer no Excel se você excluir uma regra de formatação condicional no VBA, ao contrário do que ocorre se você fizer isso pelo front-end do Excel.

Alteração de uma Regra

Como as regras são uma coleção de objetos com base em um intervalo especificado, você pode facilmente fazer alterações em regras específicas usando o VBA. As propriedades reais, depois que a regra é adicionada, são somente leitura, mas você pode usar o método Modify para alterá-las. Propriedades como cores são de leitura/gravação.

Sub ExemploAlteracaoFormatacaoCondicional()
Dim MeuIntervalo As Range
'Criar Objeto Range
Set MeuIntervalo = Range("A1:A10")
'Apagar formatação condicional anterior
MeuIntervalo.FormatConditions.Delete
'Adicionar primeira regra
MeuIntervalo.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=100", Formula2:="=150"
        MeuIntervalo.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
'Alterar regra
MeuIntervalo.FormatConditions(1).Modify xlCellValue, xlLess, "10"
'Alterar cor da regra
MeuIntervalo.FormatConditions(1).Interior.Color = vbGreen
End Sub

Esse código cria um objeto Range (A1:A10) e adiciona uma regra para números entre 100 e 150. Se a condição for verdadeira, a cor da célula mudará para vermelho.

Em seguida, o código altera a regra para números menores que 10. Se a condição for verdadeira, a cor da célula mudará para verde.

Uso de um Esquema de Cores Graduadas

A formatação condicional do Excel tem um meio de usar cores graduadas em um intervalo de números em ordem crescente ou decrescente.

Isso é muito útil quando você tem dados como números de vendas por área geográfica, temperaturas de cidades ou distâncias entre cidades. Usando o VBA, você tem a vantagem adicional de poder escolher seu próprio esquema de cores graduadas, em vez das cores padrão oferecidas no front-end do Excel.

Sub CoresGraduadas()
Dim MeuIntervalo As Range
'Criar Objeto Range
Set MeuIntervalo = Range("A1:A10")
'Apagar formatação condicional anterior
MeuIntervalo.FormatConditions.Delete
'Definir tipo de escala
    MeuIntervalo.FormatConditions.AddColorScale ColorScaleType:=3
    'Selecionar cor para o menor valor no intervalo
    MeuIntervalo.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueLowestValue
    With MeuIntervalo.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 7039480
    End With
    'Selecionar cor para os valores intermediários no intervalo
    MeuIntervalo.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValuePercentile
    MeuIntervalo.FormatConditions(1).ColorScaleCriteria(2).Value = 50
   'Selecionar cor para o valor central do intervalo
    With MeuIntervalo.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 8711167
    End With
     'Selecionar cor para o maior valor do intervalo
    MeuIntervalo.FormatConditions(1).ColorScaleCriteria(3).Type = _
        xlConditionValueHighestValue
    With MeuIntervalo.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 8109667
    End With
End Sub

Quando esse código for executado, ele graduará as cores das células de acordo com os valores ascendentes no intervalo A1:A10.

Essa é uma maneira impressionante de exibir os dados e certamente chamará a atenção dos usuários.

Formatação Condicional para Valores de Erro

Quando você tem uma grande quantidade de dados, pode facilmente deixar passar um valor de erro em suas várias planilhas. Se isso for apresentado a um usuário sem ser resolvido, poderá causar grandes problemas e fazer com que o usuário perca a confiança nos números. Para isso use um tipo de regra de xlExpression e uma função do Excel de IsError para avaliar a célula.

Você pode criar um código para que todas as células com erros tenham uma cor de célula vermelha:

Sub ExemploFormatacaoCondicionalErro()
Dim MeuIntervalo As Range
'Criar Objeto Range
Set MeuIntervalo = Range("A1:A10")
'Apagar formatação condicional anterior
MeuIntervalo.FormatConditions.Delete
'Adicionar regra de erro
MeuIntervalo.FormatConditions.Add Type:=xlExpression, Formula1:="=ÉErro(A1)=VERDADEIRO"
'Definir cor de fundo como vermelho
MeuIntervalo.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End Sub

Formatação Condicional para Datas no Passado

Você pode ter dados importados em que deseja destacar datas que estão no passado. Um exemplo disso poderia ser um relatório de devedores em que você deseja que as datas de faturas antigas com mais de 30 dias sejam destacadas.

Esse código usa o tipo de regra xlExpression e uma função do Excel para avaliar as datas.

Sub ExemploFormatacaoCondicionalDataNoPassado()
Dim MeuIntervalo As Range
'Criar Objeto Range Baseado em um Coluna de Datas
Set MeuIntervalo = Range("A1:A10")
'Apagar Formatações Condicionais Anteriores
MeuIntervalo.FormatConditions.Delete
'Adicionar regra para datas no passado
MeuIntervalo.FormatConditions.Add Type:=xlExpression, Formula1:="=Agora()-A1 > 30"
'Definir cor de fundo como vermelho
MeuIntervalo.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End Sub

Esse código utilizará um intervalo de datas no intervalo A1:A10 e definirá a cor da célula como vermelha para qualquer data com mais de 30 dias no passado.

Na fórmula que está sendo usada na condição, Agora() fornece a data e a hora atuais. Isso continuará sendo recalculado toda vez que a planilha for recalculada, de modo que a formatação mudará de um dia para o outro.

Uso de Barras de Dados na Formatação Condicional em VBA

Você pode usar o VBA para adicionar barras de dados a um intervalo de números. Elas são quase como minigráficos e fornecem uma visão instantânea do tamanho dos números em relação uns aos outros. Ao aceitar valores padrão para as barras de dados, o código é muito fácil de escrever.

Sub ExemploFormatacaoBarraDeDados()
Dim MeuIntervalo As Range
Set MeuIntervalo = Range("A1:A10")
MeuIntervalo.FormatConditions.Delete
MeuIntervalo.FormatConditions.AddDatabar
End Sub

Seus dados terão a seguinte aparência na planilha:

Uso de Ícones na Formatação Condicional em VBA

É possível usar a formatação condicional para colocar ícones ao lado dos números em uma planilha. Os ícones podem ser setas, círculos ou várias outras formas. Neste exemplo, o código adiciona ícones de seta aos números com base em seus valores percentuais:

Sub ExemploConjuntoDeIcones()
Dim MeuIntervalo As Range
'Criar Objeto Range 
Set MeuIntervalo = Range("A1:A10")
'Apagar formatação condicional anterior
MeuIntervalo.FormatConditions.Delete
'Adicionar Conjunto de Ícones para o Objeto FormatConditions
MeuIntervalo.FormatConditions.AddIconSetCondition
'Definir o conjunto de ícones como setas - condição 1
With MeuIntervalo.FormatConditions(1)
    .IconSet = ActiveWorkbook.IconSets(xl3Arrows)
End With
'Definir o critério para o ícone para o valor percentual desejado - condição 2
With MeuIntervalo.FormatConditions(1).IconCriteria(2)
    .Type = xlConditionValuePercent
    .Value = 33
    .Operator = xlGreaterEqual
End With
'Definir o critério para o ícone para o valor percentual desejado - condição 3
With MeuIntervalo.FormatConditions(1).IconCriteria(3)
    .Type = xlConditionValuePercent
    .Value = 67
    .Operator = xlGreaterEqual
End With
End Sub

Isso proporcionará uma visualização instantânea mostrando se um número é alto ou baixo. Depois de executar esse código, sua planilha terá a seguinte aparência:

Uso da Formatação Condicional para Destacar os Cinco Principais

Você pode usar o código VBA para destacar os cinco principais números em um intervalo de dados. Você usa um parâmetro chamado “AddTop10”, mas pode ajustar o número de classificação no código para 5. Um usuário pode querer ver os números mais altos em um intervalo sem ter que classificar os dados primeiro.

Sub ExemploTop5()
Dim MeuIntervalo As Range
'Criar Objeto Range
Set MeuIntervalo = Range("A1:A10")
'Apagar formatação condicional anterior
MeuIntervalo.FormatConditions.Delete
    'Adicionar uma condição Top10
    MeuIntervalo.FormatConditions.AddTop10
    With MeuIntervalo.FormatConditions(1)
        'Definir parâmetro 10 primeiros itens
        .TopBottom = xlTop10Top
        'Definir apenas top 5
        .Rank = 5
    End With
    With MeuIntervalo.FormatConditions(1).Font
        'Definir a cor da fonte
        .Color = -16383844
    End With
    With MeuIntervalo.FormatConditions(1).Interior
        'Definir cor de fundo da célula
        .Color = 13551615
    End With
End Sub

Os dados em sua planilha teriam a seguinte aparência após a execução do código:

Observe que o valor de 145 aparece duas vezes, de modo que seis células são destacadas.

 

Importância dos Parâmetros StopIfTrue e SetFirstPriority

StopIfTrue é importante se um intervalo de células tiver várias regras de formatação condicional. Uma única célula dentro do intervalo pode satisfazer a primeira regra, mas também pode satisfazer as regras subsequentes. Como desenvolvedor, você pode querer que a formatação seja exibida somente para a primeira regra a que ela se refere. Outros critérios de regras podem se sobrepor e fazer alterações não intencionais se for permitido continuar na lista de regras.

O padrão desse parâmetro é True, mas você pode alterá-lo se quiser que todas as outras regras dessa célula sejam consideradas:

MeuIntervalo. FormatConditions(1).StopIfTrue = False

O parâmetro SetFirstPriority determina se essa regra de condição será avaliada primeiro quando houver várias regras para essa célula.

MeuIntervalo. FormatConditions(1).SetFirstPriority

Isso move a posição dessa regra para a posição 1 dentro da coleção de condições de formato, e quaisquer outras regras serão movidas para baixo com números de índice alterados. Tome cuidado se estiver fazendo alterações nas regras no código usando os números de índice. É preciso ter certeza de que está alterando ou excluindo a regra correta.

Você pode alterar a prioridade de uma regra:

MeuIntervalo. FormatConditions(1).Priority=3

Isso alterará as posições relativas de quaisquer outras regras na lista de formatos condicionais.

Uso da Formatação Condicional com Referência a Outros Valores de Células

Essa é uma das coisas que a formatação condicional do Excel não pode fazer. Entretanto, você pode criar seu próprio código VBA para fazer isso.

Suponha que você tenha uma coluna de dados e que, na célula adjacente a cada número, haja algum texto que indique qual formatação deve ocorrer em cada número.

O código a seguir percorrerá sua lista de números, procurará na célula adjacente o texto de formatação e, em seguida, formatará o número conforme necessário:

Sub ReferenciaAOutraCelulaParaFormatacaoCondicional()

'Criar variáveis para gravar o número de linhas para os dados tabulares
Dim RRow As Long, N As Long

'Capturar o número de linhas dentro do intervalo dos dados tabulares
RRow = ActiveSheet.UsedRange.Rows.Count

'Rodar por todas as linhas do intervalo de dados tabulares
For N = 1 To RRow
   
    'Usar uma instrução Select Case para avaliar a formatação com base na coluna 2
    Select Case ActiveSheet.Cells(N, 2).Value
        'Mudar a cor do interior da célula para azul
        Case "Azul"
        ActiveSheet.Cells(N, 1).Interior.Color = vbBlue
        'Mudar a cor do interior da célula para vermelho
        Case "Vermelho"
        ActiveSheet.Cells(N, 1).Interior.Color = vbRed
        'Mudar a cor do interior da célula para verde
        Case "Verde"
        ActiveSheet.Cells(N, 1).Interior.Color = vbGreen
    End Select
Next N
End Sub

Depois que esse código tiver sido executado, sua planilha terá a seguinte aparência:

As células a que se refere a formatação podem estar em qualquer lugar da planilha ou mesmo em outra planilha da pasta de trabalho. Você pode usar qualquer forma de texto para criar uma condição para a formatação, e a sua imaginação é o único limite para o uso desse código.

Operadores que Podem ser Usados em Instruções de Formatação Condicional

Como você viu nos exemplos anteriores, os operadores são usados para determinar como os valores da condição serão avaliados, por exemplo, xlBetween.

Há vários desses operadores que podem ser usados, dependendo de como você deseja especificar os critérios da regra.

Nome do operador	Valor	Descrição
xlBetween	1	Entre. Pode ser usado somente se duas fórmulas forem fornecidas.
xlEqual	3	Igual.
xlGreater	5	Maior que.
xlGreaterEqual	7	Maior que ou igual a.
xlLess	6	Menor que.
xlLessEqual	8	Menor que ou igual a.
xlNotBetween	2	Não entre. Pode ser usado somente se duas fórmulas forem fornecidas.
xlNotEqual	4	Não é igual.