Formatação condicional do Excel
A Formatação Condicional do Excel permite definir regras que determinam a formatação das células.
Por exemplo, você pode criar uma regra que destaca células que atendem a determinados critérios. Exemplos incluem:
- Números que se enquadram em um determinado intervalo (por exemplo, menos de 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 Início> Estilos (ALT> H> L).
Para criar sua própria regra, clique em ‘Nova Regra’ e uma nova janela aparecerá:
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 de dentro do código VBA, seus novos parâmetros aparecerão na janela de formatação condicional do front-end do Excel e ficarão visíveis para o usuário. O usuário poderá editá-los ou excluí-los, 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 planilha específica e a um determinado intervalo de células. Se forem necessários em outra parte da pasta de trabalho, também devem ser configurados nessa planilha.
Usos práticos de formatação condicional em VBA
Você pode ter uma grande quantidade de dados brutos importados para sua planilha de um arquivo CSV (valores separados por vírgula) ou de uma tabela ou consulta de banco de dados. Isso pode fluir para um painel ou relatório, com a alteração dos números importados de um período para outro.
Quando um número muda e está fora de um intervalo aceitável, convém destacar isso, por exemplo, cor de fundo da célula em vermelho, e você pode fazer isso configurando a formatação condicional. Dessa forma, o usuário será atraído instantaneamente para esse número e poderá então 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 haja uma razão perfeitamente boa para um número incomum, mas quando o usuário apresenta o painel ou relatório para um nível superior de gerenciamento, ele deseja poder remover os "alarmes".
Além disso, nos dados brutos importados, você pode destacar onde os números são ridiculamente grandes ou ridiculamente pequenos. O intervalo de dados importado 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 formatação condicional apenas para esse intervalo.
Você também pode ter uma situação em que haja uma lista classificada de nomes com valores numéricos em relação a cada um, por exemplo, salário de funcionário, notas de exame. Com a formatação condicional, você pode usar cores graduadas para ir do mais alto para o mais baixo, o que parece muito impressionante para fins de apresentação.
No entanto, 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 mudanças 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.
| 1234567891011121314 | Sub ConditionalFormattingExample ()‘Definir intervaloDim MyRange As RangeDefinir MyRange = Range (“A1: A10”)‘Excluir formatação condicional existente do intervaloMyRange.FormatConditions.Delete‘Aplicar formatação condicionalMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Fórmula1: = "= 100", Fórmula2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)End Sub |
Observe que primeiro definimos o intervalo MyRange para aplicar a formatação condicional.
Em seguida, excluímos qualquer formatação condicional existente para o intervalo. Esta é uma boa ideia para evitar que a mesma regra seja adicionada cada vez que o código é executado (é claro que não será apropriado em todas as circunstâncias).
As cores são fornecidas por valores numéricos. É uma boa ideia usar a notação RGB (Vermelho, Verde, Azul) para isso. Você pode usar constantes de cores padrão para isso, por exemplo, vbRed, vbBlue, mas você está limitado a oito opções de cores.
Existem 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 é de 0 a 255.
Observe que o parâmetro 'xlBetween' é inclusivo, portanto, os valores das células de 100 ou 150 irão satisfazer a condição.
Formatação Multi-Condicional
Você pode querer configurar várias regras condicionais dentro do seu intervalo de dados para que todos os valores em um intervalo sejam cobertos por diferentes condições:
| 12345678910111213141516171819 | Sub MultipleConditionalFormattingExample ()Dim MyRange As Range'Criar objeto de alcanceDefinir MyRange = Range (“A1: A10”)'Excluir formatos condicionais anterioresMyRange.FormatConditions.Delete'Adicionar primeira regraMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Fórmula1: = "= 100", Fórmula2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Adicionar segunda regraMyRange.FormatConditions.Add Type: = xlCellValue, Operador: = xlLess, _Fórmula1: = "= 100"MyRange.FormatConditions (2) .Interior.Color = vbBlue'Adicionar terceira regraMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlGreater, _Fórmula1: = "= 150"MyRange.FormatConditions (3) .Interior.Color = vbYellowEnd Sub |
Este exemplo configura a primeira regra como antes, com a cor da célula em vermelho se o valor da célula estiver entre 100 e 150.
Mais duas regras são então adicionadas. 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 as células em branco estiverem neste intervalo, elas serão mostradas 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 dentro do código. É muito importante que haja várias regras para obter a ordem de execução correta, caso contrário, os resultados podem ser imprevisíveis.
| 1234567891011121314151617181920212223 | Sub MultipleConditionalFormattingExample ()Dim MyRange As Range'Criar objeto de alcanceDefinir MyRange = Range (“A1: A10”)'Excluir formatos condicionais anterioresMyRange.FormatConditions.Delete'Adicionar primeira regraMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = _"= LEN (TRIM (A1)) = 0"MyRange.FormatConditions (1) .Interior.Pattern = xlNone'Adicionar segunda regraMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Fórmula1: = "= 100", Fórmula2: = "= 150"MyRange.FormatConditions (2) .Interior.Color = RGB (255, 0, 0)'Adicionar terceira regraMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlLess, _Fórmula1: = "= 100"MyRange.FormatConditions (3) .Interior.Color = vbBlue'Adicionar quarta regraMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlGreater, _Fórmula1: = "= 150"MyRange.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 age da mesma maneira que uma coleção com o índice começando em 1. Você pode iterar por meio desse objeto usando um For … Next ou For … Each loop.
Excluindo uma regra
Às vezes, você pode precisar excluir uma regra individual em um conjunto de regras múltiplas se não atender aos requisitos de dados.
| 12345678910111213 | Sub DeleteConditionalFormattingExample ()Dim MyRange As Range'Criar objeto de alcanceDefinir MyRange = Range (“A1: A10”)'Excluir formatos condicionais anterioresMyRange.FormatConditions.Delete'Adicionar primeira regraMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Fórmula1: = "= 100", Fórmula2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Excluir regraMyRange.FormatConditions (1) .DeleteEnd Sub |
Este código cria uma nova regra para o intervalo A1: A10 e a exclui. Você deve usar o número de índice correto para a exclusão, então 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 de se você fizer isso por meio do front-end do Excel.
Mudando 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 após a adição da regra são somente leitura, mas você pode usar o método Modify para alterá-las. Propriedades como cores são de leitura / gravação.
| 123456789101112131415 | Sub ChangeConditionalFormattingExample ()Dim MyRange As Range'Criar objeto de alcanceDefinir MyRange = Range (“A1: A10”)'Excluir formatos condicionais anterioresMyRange.FormatConditions.Delete'Adicionar primeira regraMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Fórmula1: = "= 100", Fórmula2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Alterar regraMyRange.FormatConditions (1) .Modify xlCellValue, xlLess, "10"‘Alterar a cor da regraMyRange.FormatConditions (1) .Interior.Color = vbGreenEnd Sub |
Este código cria um objeto de intervalo (A1: A10) e adiciona uma regra para números entre 100 e 150. Se a condição for verdadeira, a cor da célula muda para vermelho.
O código então altera a regra para números menores que 10. Se a condição for verdadeira, a cor da célula agora muda para verde.
Usando um esquema de cores graduado
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 da cidade ou distâncias entre as cidades. Usando o VBA, você tem a vantagem adicional de poder escolher seu próprio esquema de cores graduado, em vez dos padrões oferecidos no front-end do Excel.
| 1234567891011121314151617181920212223242526272829 | Sub GraduatedColors ()Dim MyRange As Range'Criar objeto de alcanceDefinir MyRange = Range (“A1: A10”)'Excluir formatos condicionais anterioresMyRange.FormatConditions.Delete'Definir tipo de escalaMyRange.FormatConditions.AddColorScale ColorScaleType: = 3'Selecione a cor para o valor mais baixo no intervaloMyRange.FormatConditions (1) .ColorScaleCriteria (1) .Type = _xlConditionValueLowestValueCom MyRange.FormatConditions (1) .ColorScaleCriteria (1) .FormatColor.Color = 7039480Terminar com'Selecione a cor para os valores médios no intervaloMyRange.FormatConditions (1) .ColorScaleCriteria (2) .Type = _xlConditionValuePercentileMyRange.FormatConditions (1) .ColorScaleCriteria (2) .Value = 50'Selecione a cor para o ponto médio da faixaCom MyRange.FormatConditions (1) .ColorScaleCriteria (2) .FormatColor.Color = 8711167Terminar com'Selecione a cor para o valor mais alto na faixaMyRange.FormatConditions (1) .ColorScaleCriteria (3) .Type = _xlConditionValueHighestValueCom MyRange.FormatConditions (1) .ColorScaleCriteria (3) .FormatColor.Color = 8109667Terminar comEnd Sub |
Quando este código é executado, ele gradua as cores das células de acordo com os valores crescentes no intervalo A1: A10.
Esta é uma maneira muito 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 perder um valor de erro em suas várias planilhas. Se isso for apresentado a um usuário sem ser resolvido, pode levar a grandes problemas e o usuário perder a confiança nos números. Isso usa um tipo de regra de xlExpression e uma função do Excel IsError para avaliar a célula.
Você pode criar um código para que todas as células com erros em tenham a cor vermelha:
| 1234567891011 | Sub ErrorConditionalFormattingExample ()Dim MyRange As Range'Criar objeto de alcanceDefinir MyRange = Range (“A1: A10”)'Excluir formatos condicionais anterioresMyRange.FormatConditions.Delete'Adicionar regra de erroMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = "= IsError (A1) = true"'Defina a cor do interior para vermelhoMyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)End Sub |
Formatação condicional para datas no passado
Você pode ter dados importados onde deseja destacar datas que estão no passado. Um exemplo disso poderia ser um relatório de devedores em que você deseja que quaisquer datas de faturas antigas com mais de 30 dias se destaquem.
Este código usa o tipo de regra de xlExpression e uma função do Excel para avaliar as datas.
| 1234567891011 | Sub DateInPastConditionalFormattingExample ()Dim MyRange As Range'Criar objeto de intervalo com base em uma coluna de datasDefinir MyRange = Range (“A1: A10”)'Excluir formatos condicionais anterioresMyRange.FormatConditions.Delete'Adicionar regra de erro para datas no passadoMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = "= Now () - A1> 30"'Defina a cor do interior para vermelhoMyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)End Sub |
Este código terá um intervalo de datas no intervalo A1: A10 e definirá a cor da célula como vermelho para qualquer data que esteja acima de 30 dias no passado.
Na fórmula usada na condição, Now () fornece a data e a hora atuais. Isso continuará recalculando toda vez que a planilha for recalculada, de modo que a formatação mudará de um dia para o outro.
Usando barras de dados em formatação condicional VBA
Você pode usar o VBA para adicionar barras de dados a um intervalo de números. Eles são quase como minigráficos e fornecem uma visão instantânea de como os números são grandes em relação uns aos outros. Ao aceitar os valores padrão para as barras de dados, o código é muito fácil de escrever.
| 123456 | Sub DataBarFormattingExample ()Dim MyRange As RangeDefinir MyRange = Range (“A1: A10”)MyRange.FormatConditions.DeleteMyRange.FormatConditions.AddDatabarEnd Sub |
Seus dados ficarão assim na planilha:
Usando ícones na formatação condicional VBA
Você pode usar a formatação condicional para colocar ícones ao lado dos números em uma planilha. Os ícones podem ser setas ou 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:
| 12345678910111213141516171819202122232425 | Sub IconSetsExample ()Dim MyRange As Range'Criar objeto de alcanceDefinir MyRange = Range (“A1: A10”)'Excluir formatos condicionais anterioresMyRange.FormatConditions.Delete'Adicionar conjunto de ícones ao objeto FormatConditionsMyRange.FormatConditions.AddIconSetCondition'Defina o conjunto de ícones para setas - condição 1Com MyRange.FormatConditions (1).IconSet = ActiveWorkbook.IconSets (xl3Arrows)Terminar com'definir os critérios do ícone para o valor percentual exigido - condição 2Com MyRange.FormatConditions (1) .IconCriteria (2).Type = xlConditionValuePercent.Value = 33.Operator = xlGreaterEqualTerminar com'definir os critérios do ícone para o valor percentual exigido - condição 3Com MyRange.FormatConditions (1) .IconCriteria (3).Type = xlConditionValuePercent.Value = 67.Operator = xlGreaterEqualTerminar comEnd Sub |
Isso dará uma visão instantânea mostrando se um número é alto ou baixo. Depois de executar este código, sua planilha ficará assim:
Usando a formatação condicional para destacar os cinco primeiros
Você pode usar o código VBA para destacar os 5 principais números em um intervalo de dados. Você usa um parâmetro chamado 'AddTop10', mas pode ajustar o número da classificação dentro do código para 5. Um usuário pode desejar ver os números mais altos em um intervalo sem ter que classificar os dados primeiro.
| 1234567891011121314151617181920212223 | Sub Top5Example ()Dim MyRange As Range'Criar objeto de alcanceDefinir MyRange = Range (“A1: A10”)'Excluir formatos condicionais anterioresMyRange.FormatConditions.Delete'Adicionar uma condição Top10MyRange.FormatConditions.AddTop10Com MyRange.FormatConditions (1)'Definir parâmetro superior para inferior.TopBottom = xlTop10Top'Set top 5 only.Rank = 5Terminar comCom MyRange.FormatConditions (1) .Font'Defina a cor da fonte.Color = -16383844Terminar comCom MyRange.FormatConditions (1) .Interior'Defina a cor do fundo da célula.Color = 13551615Terminar comEnd Sub |
Os dados em sua planilha ficariam assim depois de executar o código:
Observe que o valor de 145 aparece duas vezes, portanto, seis células são destacadas.
Significado 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 ele exiba a formatação apenas para a primeira regra correspondente. Outros critérios de regra podem se sobrepor e fazer alterações não intencionais se for permitido continuar na lista de regras.
O padrão neste parâmetro é True, mas você pode alterá-lo se quiser que todas as outras regras para aquela célula sejam consideradas:
| 1 | MyRange. 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.
| 1 | MyRange. 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 os números de índice alterados. Cuidado se você estiver fazendo alterações nas regras do código usando os números de índice. Você precisa ter certeza de que está alterando ou excluindo a regra certa.
Você pode alterar a prioridade de uma regra:
| 1 | MyRange. FormatConditions (1) .Priority = 3 |
Isso mudará as posições relativas de quaisquer outras regras na lista de formatos condicionais.
Usando Formatação Condicional Referenciando Outros Valores de Célula
Isso é algo que a formatação condicional do Excel não pode fazer. No entanto, você pode criar seu próprio código VBA para fazer isso.
Suponha que você tenha uma coluna de dados e, na célula adjacente a cada número, haja algum texto que indica qual formatação deve ocorrer em cada número.
O código a seguir irá percorrer sua lista de números, procurar na célula adjacente para formatar o texto e, em seguida, formatar o número conforme necessário:
| 123456789101112131415161718192021 | Sub ReferToAnotherCellForConditionalFormatting ()'Crie variáveis para conter o número de linhas para os dados tabularesDim RRow enquanto, N enquanto'Capture o número de linhas dentro do intervalo de dados tabularRRow = ActiveSheet.UsedRange.Rows.Count'Iterar por todas as linhas no intervalo de dados tabularesPara N = 1 para RRow'Use uma declaração Select Case para avaliar a formatação com base na coluna 2Selecione Case ActiveSheet.Cells (N, 2) .Value'Transforme a cor do interior para azulEstojo "Azul"ActiveSheet.Cells (N, 1) .Interior.Color = vbBlue'Transforme a cor do interior para vermelhoCase "Vermelho"ActiveSheet.Cells (N, 1) .Interior.Color = vbRed'Transforme a cor do interior para verdeCase "Verde"ActiveSheet.Cells (N, 1) .Interior.Color = vbGreenEnd SelectPróximo NEnd Sub |
Depois que esse código for executado, sua planilha ficará assim:
As células mencionadas para 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 estabelecer uma condição para a formatação, e você está apenas limitado pela sua imaginação nos usos que você poderia fazer com este código.
Operadores que podem ser usados em declaraçõ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, xlEntre.
Existem vários desses operadores que podem ser usados, dependendo de como você deseja especificar seus critérios de regra.
| Nome | Valor | Descrição |
| xlEntre | 1 | Entre. Pode ser usado apenas se duas fórmulas forem fornecidas. |
| xlEqual | 3 | Igual. |
| xl Maior | 5 | Maior que. |
| xlGreaterEqual | 7 | Melhor que ou igual a. |
| xlLess | 6 | Menor que. |
| xlLessEqual | 8 | Menos que ou igual a. |
| xlNotBetween | 2 | Não entre. Pode ser usado apenas se duas fórmulas forem fornecidas. |
| xlNotEqual | 4 | Não igual. |
