Escrito por Helds

Como somar cores no excel


Para somar células com uma determinada cor você precisará criar uma função personalizada no VBA, para fazer isto siga os seguinte passos:

  • Abra a planilha onde deseja utilizar a função;
  • Clique em Desenvolvedor > Visual Basic (Alt+F11)*;

Imagem

  • Com o botão direito clique sobre o nome da planilha > Inserir > Módulo;

Imagem

  • Cole o código a seguir:

Public Function SOMACOR(Cor As Range, Intervalo As Range) As Double

Dim i As Range

For Each i In Intervalo

If i.Interior.Color = Cor.Interior.Color Then

SOMACOR = SOMACOR + i.Value

End If

Next

Application.Volatile

End Function

  • Insira a função SOMACOR em sua planilha;

Imagem

*Se não aparecer a guia “Desenvolvedor” clique no Botão do Office > Opções do Excel > Mais usados. Marque a caixa de opção “Mostrar guia desenvolvedor na faixa de opções”.

75 comentários em “Como somar cores no excel”

      1. heldssouza tentei fazer esta formula, tenho 100 linhas com 50 colunas marquei algumas células em cada linha em amarelo, gostaria de que no final de cada linha eu tenha a soma de quantas celulas estão em amarelo(não a soma do conteudo da celula).Poderia me orientar o que fazer ? Grato

      2. Bom dia,

        Acho que sua dúvida é muito parecida com a do Vinícios.

        “…Você pode alterar a macro para ao invés de somar o valor da célula adicionar mais 1:

        SOMARCOR=SOMARCOR +1

        “

      4. Olá,

        Você deve ter colado a função em mais de um lugar no VBA.
        Sugiro utilizar a função SOMASES ao invés desta personalizada, e depois se vc quiser utilizar cores utiliza a formatação condicional.

        Att
        Helds Souza

  2. Ola, fiz e funcionou perfeitamente, muito obrigado. Agora como faço pra que valha pra todas as planilhas que eu abrir no excel? Da forma que fiz em cada planilha que precisar da função tenho de ir no editor de macros e colar a mesma. Obrigado.

    Responder

    1. Olá Jeferson, desculpe a demora.

      Para habilitar suas macros para todas as planilhas você deve criar um arquivo pessoal de macros.

      vá em desenvolvedor/macros/gravar nova macro.

      Selecione a opção Arquivo pessoal de macros em “armazenar macro em;”.

      Caso não consiga me avisa aqui.

      Abs

      Responder

  3. Bom dia.

    E se as células que eu quero somar não estiverem em um intervalo? Como faço para descrever a fórmula, tentei =somacor(A1;D1+D2+D3+D4+D5+D6+D7+D8) mas ele da erro. Na planilha que tenho as células não estão juntas.

    Responder

      1. Oi, boa tarde, nova dúvida: estou tentando fazer como está explicando mas não consigo porque na minha planilha embora todos os valores estejam na mesma coluna (J) algumas linhas está escrito a data de vencimento, então quando tento somar separado J7:J9;J11:J13 dá erro. Como posso solucionar? Muito obrigada.

    1. Obrigado pela colaboração Roberto.

      Neste caso eu indico você utilizar a função SOMASES utilizando os critérios que você colocou na formatação condicional.

      Qualquer dúvida adicional posta aí.

      Abs

      Responder

    1. É possível criar uma macro que roda toda vez que a célula é alterada, e então compra a cor da célula e atribui o valor.

      Mas se você puder descrever melhor seu problema eu acho mudar o valor pela cor não é o melhor método.

      Responder

  7. Ola, estou fazendo uma panilha de pagamentos, como faco para atualizar automaticamente quando altero a cor da celula?
    Porque sua formula so permite o calculo em numero inteiros?

    Responder

    1. Respondendo a primeira pergunta eu adicionei o código “application.volatile” no final do script, Após alterar as cores basta dar um F9 para atualizar.

      Ou sempre vá em fórmulas>Calcular agora.

      A segunda resposta da sua pergunta é só alterar o código vba com o código que eu atualizei no post.

      Antes a função SOMACOR estava definida como “long”, então eu alterei para “double”.

      Qualquer dúvida estou à disposição.

      Abs

      Responder

      1. Boa tarde, estou muito feliz com sua explicação, mas por exemplo, não tem como essa mudança ser automática, porque fico com receio de de repente, esquecer de apertar o F9 e aí posso me confundir acreditando que determinados valores não estão compatíveis. Tem como eu mudar a cor e a mudança do valor ser automática sem eu precisar apertar o F9? Mais uma dúvida, caso eu me esqueça desta atualização, quando eu abrir a planilha de novo, e alterar os valores, e atualizar, ela atualiza tudo ? Muito obrigada.

      2. Olá Nubia, tudo bem?

        Sugiro que você utilize a função SOMASES e para colocar as cores que você precisa utilize a formatação condicional.

        Assim não precisa utilizar nenhuma função personalizada.

        Abraço

  8. Olá gostei bastante da função, mas estou com dois problemas. Estou utilizando um filtro avançado. Toda a vez que troco os dados da planilha a sua função me retorna “#VALOR”. eu corrigi este problema acrescentando o comando “Ctrl+Alt+F9” na minha macro que faz o filtro avançado. O meu outro problema é que estou somando valores em dinheiro, mas sua fórmula soma e arredonda os valores deixando de lado os centavos. Tem alguma ideia que possa me ajudar? Obrigado.

    Responder

  10. Bom dia,
    Muito bom esse seu tutorial,
    Só tenho uma dúvida, no caso eu gostaria de somar as células de uma cor, não o valor que está dentro dela, por exemplo: tenho vários apartamento vendidos (verde) e não vendidos (vermelho) quando aplico a fórmula ela soma o valor dentro da célula que é o número do apartamento , não a quantidade de vermelhos ou verdes!
    Obrigado

    Responder

    1. Bom dia,

      Ou você pode colocar 0 para não vendidos e 1 para vendidos e colocar formatação condicional verde e vermelho. Ou você pode alterar a macro para ao invés de somar o valor da célula adicionar mais 1:

      SOMARCOR=SOMARCOR +1

      Abs

      Responder

  12. HeldsSouza, boa tarde!

    Fiz o procedimento que orientou e deu certo 100 % – somacor. O problema é que quando salvei o documento deu uma mensagem ” Os recursos a seguir não podem ser salvos em pastas de trabalho sem macro. Projeto VB, para salvar um arquivo com esses recursos, clique em não e escolha o tipo de arquivo habilitado para macro na lista tipo de arquivo”. Fecho o arquivo e quando abro novamente aparace nas fórmulas #nome?. Entro no visual basic e o módulo não está mais lá. Faço o procedimento, clico F9 e tudo volta a funcionar. Não tenho experiência nenhuma com macros e não sei como resolver.

    Responder

      1. HeldsSouza, boa noite!

        Muito obrigado pelas orientações, deu certo. Desculpe-me por gastar seu tempo com coisas básicas.
        Foi de grande ajuda essa explicação.
        Desejo-lhe muita benção e sucesso!
        Jorge Torquato

  13. Muito obrigado heldssouza!
    Seu tutorial está ótimo, me ajudou muito na solução de um problema que parecei impossível.
    Parabéns!!

    Responder

  16. Bom dia,

    Inseri a macro abaixo para somar por cor, porém não estou conseguindo. A soma da sempre a mesma. Pode me ajudar?
    A PLANILHA É GRANDE.
    Function SumByColor(CellColor As Range, SumRange As Range)
    Dim myCell As Range
    Dim iCol As Integer
    Dim myTotal
    iCol = CellColor.Interior.ColorIndex ‘obtém a cor de destino
    For Each myCell In SumRange ‘verificar cada célula no intervalo designado
    ‘se a cor da célula corresponder á cor alvo
    If myCell.Interior.ColorIndex = iCol Then
    ‘adiciona o valor da célula ao total
    myTotal = WorksheetFunction.Sum(myCell) + myTotal
    End If
    Next myCell
    SumByColor = myTotal
    End Function

    Responder

  21. Olá heldssouza, poderia me ajudar?

    estou montando uma planilha com os vencimentos dos contratos dos meus clientes, usei a formatação condicional em uma coluna para colorir as células quando o contrato está no prazo (azul) e também para quando eles expiram (vermelho). quero calcular quantas células tenho de cada. e não consegui usar sua macro. como posso resolver isso?

    Responder

  22. Boa Tarde heldssouza!!!
    Consegui fazer funcionar mas estou salvando errado, pois qdo reabro a pasta de trabalho volta com erro em todas as planilhas

    Responder

    1. Olá, nunca tive contato com libreoffice, mas pelo que eu sei não é possível torná-lo compatível com o VBA, ja que é uma linguagem específica do MS Office, porém acredito que seja possível fazer macros através de outras ferrantas disponibilizadas no livreoffice.

      Abs

      Responder

    1. Para que a planilha seja atualizada, automaticamente você deverá utilizar agendamento de tarefas do windows ou outlook, após atualizada a planilha você deve criar um módulo no vba e fazer uma macro para enviar o email.

      Responder

  26. Obrigado heldsouza… 10.. mesmo… resolveu um grande problema meu.. e os comentarios me ajudaram com as outras duvidas… obrigado a todos… abç…

    Responder

  30. Boa tarde. Excelente post mas agora estou com uma dúvida: A planilha onde coloquei a sua macro possui filtros e ao alterar os filtros os intervalos mudam e a fórmula passa a dar erro. Estou tentando uma forma de localizar os extremos da seleção do filtro para tentar automatizar mais, ou seja, a cada mudança no filtro da planilha encontrar o início e o fim do intervalo de dados automaticamente, isso é possível?

    Responder

  33. Boa tarde,

    Eu consegui utilizar sua função (aliás, obrigado por tê-la compartilhado), mas não estou conseguindo deixa-la disponível para todas as planilhas, apesar de já ter salvo ela no Personal.XLSB, onde incluvise vá possuo outras Macros salvas que rodam normalmente.

    Devo acrescentar alguma coisa para que as planilhas reconheçam isso como funções públicas estando no Personal Workbook?

    Obrigado

    Responder

    1. Olá,

      Teria que analisar para te responder. Utilizar esta função não é muito eficiente. Sugiro criar uma coluna texto com categorias que represente as suas cores e utilizar a função SOMASES para fazer a soma. Se preferir pode utilizar a formatação condicional para pintar nas cores que você desejar baseado na coluna de categorias.

      Abs

      Responder

  34. Boa noite Heldssouza, e a todos; Otimo post, com ele consegui visualizar erros nas minhas tabelas de controle de gastos na empresa separando por cor cada tipo de gastos e com isto obter resumos mais eficiente muito obrigado por facilitar o uso do exel que tem milhares de recursos.

    Responder

Deixar mensagem para vanda Cancelar resposta