Aprenda duas maneiras de alterar a cor de uma célula no Excel com base no valor da célula
Neste artigo, apresento duas maneiras rápidas de alterar a cor de segundo plano das células, com base em um valor no Excel. Também, vou mostrar aqui algumas opções de fórmulas do Excel que alteram a cor das células em branco e das células apresentam erros.
Todo mundo sabe que alterar a cor de plano de fundo de uma célula ou um intervalo de dados no Excel é fácil, basta clicar no botão “Preencher cor”. Mas, e se a gente quiser alterar a cor de fundo de todas as células com um determinado valor? E se, você quiser que a cor do plano de fundo seja alterada automaticamente juntamente com as alterações do valor da célula? Para sua surpresa, neste artigo, você encontrará respostas para essas perguntas e aprenderá algumas dicas úteis que te ajudarão a escolher o método certo para cada tarefa específica.
Como alterar a cor da célula com base em um valor no Excel dinamicamente
Situação: A cor do plano de fundo muda a depender do valor da célula.
Tarefa: Imagine que você possua uma tabela ou intervalo de dados e deseja alterar a cor de segundo plano das células com base em um determinado valor. Além disso, você deseja que a cor mude dinamicamente, refletindo as alterações nos dados.
Solução: é preciso usar formatação condicional do Excel para destacar os valores maiores que X, menores que Y ou entre X e Y.
Suponha que você tenha uma lista de preços da gasolina em diferentes estados e deseje que os preços superiores a R$ 3,75 sejam da cor vermelha e, iguais ou menores que R$ 3,45 sejam da cor verde.
Nota: As capturas de tela deste exemplo foram capturadas no Excel 2016, no entanto, os botões, caixas de diálogo e configurações são iguais ou quase iguais no Excel 2010 e no Excel 2013.
Aqui está o passo a passo de como fazer isso:
1 – Selecione a tabela ou o intervalo em que deseja alterar a cor de fundo das células. Neste exemplo, selecionamos $B$2: $H$10 (os nomes das colunas e a primeira coluna que listam os nomes dos estados são excluídos da seleção).
2 – Navegue até a guia Início, grupo Estilos e escolha Formatação condicional> Nova regra….
3 – Na caixa de diálogo Nova regra de formatação, selecione a opção Formatar apenas células que contenham em Selecione um tipo de regra na parte superior da caixa de diálogo.
4 – Na parte inferior da caixa de diálogo em Formatar somente células com, defina as condições da regra. Optamos por formatar apenas células com um valor de célula maior que 3,7, como você pode ver na captura de tela abaixo.
Em seguida, clique no botão Formatar… para escolher a cor de fundo a ser aplicada quando a condição acima for atendida.
5 – Na caixa de diálogo Formatar células, alterne para a guia Preenchimento e selecione a cor de sua escolha, no nosso caso, a cor avermelhada. Clique em OK.
6 – Agora, de volta à janela Nova Regra de Formatação, a visualização das alterações no formato é exibida na caixa Visualizar. Se tudo estiver correto, clique no botão OK.
O resultado da formatação será parecido com este:
Como precisamos aplicar mais uma condição, ou seja, alterar o plano de fundo das células com valores iguais ou inferiores a 3,45 para a cor verde, clique no botão Nova Regra novamente e repita as etapas 3 a 6, definindo a condição necessária. E, aqui está a visualização da nossa segunda regra de formatação condicional:
Dica: Você pode usar o mesmo método para alterar a cor da fonte com base no valor da célula. Para fazer isso, basta alternar para a guia Fonte na caixa de diálogo Formatar células que discutimos na etapa 5 e escolher sua cor de fonte preferida.
Para ter acesso ao conteúdo completo do tópico Formatação Condicional no Excel, acesso esse link aqui!!!
Como alterar permanentemente a cor de uma célula com base no seu valor atual
Situação: Uma vez definida, a cor do plano de fundo não será alterada, independentemente de como o conteúdo da célula possa ser alterado no futuro.
Tarefa: Imagine que você queira colorir uma célula com base em seu valor atual e deseja que a cor do plano de fundo permaneça a mesma, mesmo quando o valor da célula for alterado.
Solução: Encontre todas as células com um determinado valor ou valores usando a função Localizar Tudo do Excel ou Selecionar Células Especiais e altere o formato das células encontradas usando o recurso Formatar Células.
Essa é uma daquelas tarefas raras que ninguém aborda nos arquivos de ajuda, fóruns e blogs do Excel e que, normalmente não existe uma solução direta para elas porque de fato, não se trata de uma tarefa típica. Mas, ainda assim, caso seja necessário alterar estaticamente a cor de fundo das células, a menos que alguém a mude manualmente novamente, continue com as etapas a seguir.
Encontre e selecione todas as células que atendem a uma determinada condição
Dependendo do tipo de valores que são procurados, existem vários cenários possíveis para executar essa prática.
Para colorir células com um valor específico, por exemplo, 50, 100 ou 3,4, vá para a guia Página Inicial, grupo Edição, e clique em Localizar Selecionar> Localizar….
No exemplo anterior, para encontrar todos os preços da gasolina entre 3,7 e 3,799 podemos especificar os critérios de pesquisa abaixo:
No entanto, o que realmente precisamos é “encontrar todos os preços da gasolina superiores a 3,7 e infelizmente, a caixa de diálogo Localizar e substituir do Excel não permite fazer isso.
Para confirmar clique em OK.
Mude a cor do plano de fundo de células selecionadas usando a caixa de diálogo “Formatar células”
> Grupo de células> Formatar> Formatar células …) e fazer todas as alterações de formato desejados.
Alterar cor do plano de fundo de células especiais (espaços em branco, ou erros de fórmula)
Use fórmulas do Excel para alterar cor do plano de fundo de células especiais
Insira uma das seguintes fórmulas em Formatar valores em que esta fórmula é verdadeira:
= ÉCEL.VAZIA ( ) - para alterar a cor de fundo das células em branco.
= ÉERROS ( ) - para alterar a cor de fundo das células com fórmulas que retornam erros.
= ÉCEL.VAZIA (B2:H12)
Alterar a cor do plano de fundo das células especiais estaticamente
Uma vez alterada, a cor do plano de fundo permanecerá a mesma, independentemente das alterações dos valores da célula.
Se você deseja alterar a cor das células em branco ou células com erros de fórmula permanentemente, siga este caminho:
1 – Selecione sua tabela ou um intervalo e pressione F5 para abrir a caixa de diálogo Ir para e clique no botão Especial ….
2 – Na caixa de diálogo Ir para especial, marque o botão de opção Em branco para selecionar todas as células vazias.
Caso deseje destacar células que contenham fórmulas com erros, escolha as opções Fórmulas> Erros. Como podemos ver na imagem acima, várias outras opções estão disponíveis para deferentes situações.
3 – Finalmente, altere o plano de fundo das células selecionadas ou faça outras personalizações de formato usando a caixa de diálogo Formatar células, conforme descrito em :Alterando o plano de fundo das células selecionadas.
Lembre-se de que as alterações de formatação feitas dessa maneira persistirão mesmo se as células em branco forem preenchidas com dados ou erros de fórmula já corrigidas. Obviamente, é difícil imaginar por que alguém pode querer tê-lo dessa maneira, penso eu que seja para fins históricos 🙂
Bom, por enquanto é tudo! Espero vê-lo no meu blog, novamente, na próxima semana!
Conclusão
Neste artigo vimos duas maneiras diferentes de alterar a cor plano de fundo do Excel, com base em um valor de célula.
O site deste pessoal aqui foi referência para este artigo. O site é muitoooo bacana com várias dicas legais para Excel, mas infelizmente o conteúdo está em inglês, o que não deixa de ser uma boa referência para quem conseguir acompanhar. Eu recomendo.
Eu sei que não é comum ler um artigo deste, como tutorial, e repetir para aprender, mas recomendo fortemente que você tome coragem de repetir todo o passo-a-passo. Eu penso que praticar é a melhor maneira de aprender e, embora pareça simples, é preciso se acostumar a navegar entre as telas e fórmulas do Excel para que no futuro você possa se tornar um usuário avançado.
Portanto, continue lendo mais sobre Excel básico aqui.
Olá, Érica!
Gostaria de uma ajudinha. Tenho um planilha em que necessito que a cor da célula mude automaticamente quando a mesma ultrapassar a validade de um ano, por exemplo, tenho um treinamento que tem validade de um ano, o colaborador fez esse treinamento dia 22/02/2019, hoje sendo 23/02/2020 gostaria que essa celular ficasse vermelha para poder ficar mais fácil a visualização de quais certificados estão expirados.
Bom dia Renan!
Para fazer o que você deseja, você vai precisar trabalhar com Formatação Condicional. Para ter acesso ao material acesse aqui!
Primeiramente, use uma célula da sua planilha como referência e insira o valor da data atual: =HOJE()
Em uma das colunas, você provavelmente vai ter o nome do colaborador da empresa e em uma outra coluna a data de vencimento do curso de cada um deles.
Talvéz, visualizando a planilha que eu criei para você fique mais facil de entender. Para fazer download clique aqui!!
Pegando como exemplo a minha planilha, selecione o intervalo de A4:B11 e clique em “Formatação Condicional” > “Nova Regra” > “Usar uma fórmula para determinar …”
Por fim, digite a fórmula: =$B4< $B$1 e formate da maneira que desejar. No meu caso coloquei preenchimento vermelho. Para concluir, confirme com um "OK" e verá o resultado.Para visualizar a formatação, assim que você fizer download da planilha, clique em uma das células formatadas e com o mouse vá até "Formatação Codicional" > “Gerenciar Regras”.
Nesta condição, você pode além de visualizar, formatar a fórmula da maneira que desejar.
Espero ter ajudado!
Olá Érica tudo bem !!!
Estou com planilha em formatação condicional para conferência de jogos
São 4 sorteios , das dezenas de 0 a 100, porém os números podem se repetir, quando eu lanço números sorteados automaticamente ele pinta a celular correspondente ao número sorteado e contabiliza o número de acerto até momento, quando mesmo número é sorteado ai que vem problema que não consigo resolver, é possível contar só células preenchidas ou usar algum critério que não contém os números repetidos, por favor me ajude ja procurei por tudo e ainda não conseguir resolver isso
Olá Eduardo,
Desculpe a demora para responder. Entendi qual seu problema pela planilha no google sheets que enviou por email.
No Excel é possível contar células usando como critério a cor do fundo da célula da seguinte maneira:
Função em VBA:
Function CountCcolor(range_data As Range, criteria As Range) As Long
Dim datax As Range
Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
If datax.Interior.ColorIndex = xcolor Then
CountCcolor = CountCcolor + 1
End If
Next datax
End Function
O primeiro parâmetro é o intervalo de células que estão mudando de cor por formatação condicional e o segundo critério é a cor que você escolheu. No caso selecione uma célula qualquer com a cor idêntica ao do critério que escolheu na formatação condicional.
A referência que você pode ler sobre está aqui:
https://docs.microsoft.com/en-us/previous-versions/office/troubleshoot/office-developer/count-cells-number-with-color-using-vba
Vi pouco do seu site, mas gostei MUITO! Parabens pelo trabalho!
Estou com uma duvida que não consigo sanar em lugar algum… Me parece que para voce seria piece of cake!
Tenho uma planilha de fluxo de caixa. Gostaria que uma parte da coluna B ficasse em branco, quando parte da coluna A não tenha nenhum valor. Pensei em usar a função =se(écel.vazia(A)….. , mas nao sei como terminar para que parte da coluna B fique branca, quando células da coluna A estiverem vazias.. Me fiz claro?
Obrigado desde já e continue o excelente trabalho! Parabens
Boa noite Mauricio .
Primeiramente obrigada pelo seu comentário.
Neste caso, sugiro trabalhar com a seguinte função: =SE(A1=””;””;”na coluna A contem uma valor”)
Como no exemplo abaixo:
Espero ter ajudado!