Excel Rodar macro quando o conteúdo da célula é alterado via fórmula

[Yskhadar]

Boa noite a todos.

Venho buscar uma solução para meu problema e peço a ajuda dos feras aqui.
Eu tenho uma mapa de vendas por estado e tenho uma macro que pinta os estados onde houve venda.

A macro busca as cores numa tabela em A4:B30, onde na coluna A estão os estados e na coluna B as quantidades.
Conforme ocorrem vendas eu clico no botão da macro e atualiza. Perfeito.

Agora eu quero mudar jeito de atualizar, eu gostaria que essa macro colorisse o mapa automaticamente quando nova venda fosse realizada em um estado ainda não pintado.
Procurei mas só achei um modo de fazer com que seja executada a macro se as células forem atualizadas manualmente...
O código que achei foi:

 

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = True

If Not Intersect(Target, Range("b4:b30")) Is Nothing Then

Call ColorirMapa

End If

End Sub

 

Existe uma maneira de ser automático?
Agradeço muito a ajuda.

[Visitante]

8 horas atrás, Yskhadar disse:

Agora eu quero mudar jeito de atualizar, eu gostaria que essa macro colorisse o mapa automaticamente quando nova venda fosse realizada em um estado ainda não pintado.

A Formatação Condicional pode fazer esse trabalho.

 

Existe uma maneira de ser automático?
Se há fórmulas em B4:B30 então experimente o código abaixo.

 

Private Sub Worksheet_Calculate()
 ColorirMapa
End Sub

 

[Yskhadar]

@osvaldomp

 

Como sempre, você é o cara!

Muitíssimo obrigado.

Só tive que ajustar a macro ColorirMapa.

você falou que a formatação condicional pode fazer esse trabalho... de que forma seria?

 

[Visitante]

 

Para aplicar a Formatação Condicional:

1. selecione o intervalo desejado, por exemplo, B4:B30

2. menu Página Inicial / Formatação Condicional / Nora Regra / Usar uma fórmula ...

3. na caixa de fórmula cole ~~~> =$B3>0 ~~~> formate como desejar, ou

se as fórmulas em B4:B30 podem retornar também "vazio", então coloque esta ~~~> =E($B3<>"";$B3>0), ou utilize uma fórmula com o critério igual ao critério adotado na macro ColorirMapa

 

Se você não conseguir então disponibilize aqui no fórum uma amostra do seu arquivo Excel e informe com exatidão qual o critério para colorir, ou disponibilize o código ColorirMapa.

[Yskhadar]

Segue um arquivo para melhor entendimento.

Do jeito que está hoje funciona com a macro e a solução também funciona desde que eu não mova o mapa de aba.

Entretanto é o que eu preciso. Que o mapa vá para outra aba e tenha a atualização de forma automática mantida.

Grato

Consulta_clube.7z

[Visitante]

Considerei que você irá mover o mapa para a planilha Painel, conforme o seu objetivo.

 

1. instale uma cópia do código abaixo no módulo da planilha Mapa

Private Sub Worksheet_Calculate()
 ColorirMapaV2
End Sub

 

2. instale um cópia do código abaixo em um módulo comum

Sub ColorirMapaV2()
 Dim Estado As Range, Celula As String
  For Each Estado In Sheets("Mapa").Range("A4:A30")
   Celula = Sheets("Mapa").Cells(Estado.Row, 3)
   Sheets("Painel").Shapes(Estado.Value).Fill.ForeColor.RGB = Sheets("Mapa").Range(Celula).Interior.Color
  Next Estado
End Sub

 

funcionamento - qualquer alteração manual efetuada na coluna C da planilha Vendas irá provocar o recálculo da planilha Mapa, devido às fórmulas da sua coluna B, e o recálculo irá provocar o disparo do primeiro código acima que irá chamar o código ColorirMapaV2 que irá pintar o mapa localizado na planilha Painel.

 

Segue uma sugestão alternativa, já considerando o mapa movido:

1. aplicar Formatação Condicional no intervalo A4:B30 da planilha Mapa (ao aplicar a FC você poderá escolher cores diferentes para cada faixa de vendas. Por exemplo, branco para vendas zero, azul de 1 a 15, amarelo de 16 a 30 e verde de 31 a 45. Ainda, poderá subdividir, por exemplo, 31 a 35 verde claro, 36 a 40 verde médio e 41 a 45 verde escuro)

2. colocar um código WS_Change para captar alterações na coluna C da planilha Vendas, que irá verificar em qual UF houve alteração, que irá copiar a sua respectiva cor na FC comentada acima, e irá alterar a cor somente daquela UF no mapa.

 

Esta alternativa basicamente oferece a possibilidade de pintar o mapa com múltiplas cores com base nas vendas por UF e também de tratar no mapa somente da UF alterada, e não percorrer todas as UFs a cada alteração como é hoje.

Ainda, a coluna auxiliar C da planilha Mapa não mais seria utilizada e se você quiser passar o intervalo A4:B30 para outra planilha, então Mapa poderá ser excluída.

Retorne se houver interesse.

 

[Yskhadar]

@osvaldomp como sempre, funcionou perfeitamente.

A princípio este mapa pintado mostra apenas os locais onde a marca já alcançou, sem levar em conta vendas.

Mas acho que isso pode ser mudado, ou acrescentado.

A solução ou alternativa que você falou é interessante, gostaria de saber mais sim.

Já adiantando, são dois produtos, com alguns modelos, mas apenas 2 produtos.

[Visitante]

1 hora atrás, Yskhadar disse:

 

A princípio este mapa pintado mostra apenas os locais onde a marca já alcançou, sem levar em conta vendas.

 

 

A sugestão que passei baseia-se no uso de múltiplas cores no mapa, mas me parece que não é o seu caso, pois você utiliza somente duas cores, então a sugestão não faz sentido.

[Yskhadar]

Sim, como falei a princípio só onde a marca chegou.

Mas estou pensando e acho que seria interessante fazer o indicado baseado nas vendas.

Se quiser perder um pouco do seu tempo acho que seria interessante...
Talvez eu precise te mandar a planilha de outro jeito...

De qualquer forma, a solução que eu buscava você me deu e sou grato.

É que daí você plantou uma semente...

[Visitante]

 

12 minutos atrás, Yskhadar disse:

Talvez eu precise te mandar a planilha de outro jeito...

Ok, aguardo.

 

2 horas atrás, Yskhadar disse:

Já adiantando, são dois produtos, com alguns modelos, mas apenas 2 produtos.

E também explique esta parte, pois eu não entendi.

 

[Yskhadar]

@osvaldomp

 

Segue a planilha com acréscimos das vendas.

A parte que você não entendeu, são 2 produtos, sapatos e bolsas, mas cada um tem alguns modelos. Então sçao subdivisões, mas acho que não vai alterar o resultado.

Obrigado

Consulta_clube1.7z

[Visitante]

1. faltou você informar qual o resultado desejado

2. faltou você informar em quais dados o resultado será baseado

3. na planilha Painel ainda não há um mapa

4. as fórmulas da planilha Mapa perderam as referências (imagem abaixo)

 

[Yskhadar]

Desculpa a confusão.

A planilha era a mesma da anterior, só acresci informações e não refiz as referências.

O Mapa funcionou do jeito que você fez, só não alterei neste arquivo, porque acredito que será modificado.

Seguindo sua sugestão, seria formatar o Mapa levando em consideração as vendas (coluna D) por estado (coluna H).

E se possível levando em consideração segmento do produto (coluna B).

Grato

Consulta_clube1.7z

[Visitante]

Instale uma cópia do código abaixo no módulo da planilha Vendas. Válido para o arquivo do post #13.

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim LR As Long, cor As Long
  If Target.Count > 1 Then Exit Sub
  If Target.Column <> 4 And Target.Column <> 8 Or Cells(Target.Row, 8) = "" Then Exit Sub
  LR = Cells(Rows.Count, 8).End(3).Row
  Select Case Evaluate("=SUMIF(H4:H" & LR & "," & Cells(Target.Row, 8).Address & "," & "D4:D" & LR & ")")
   Case Is > 40: cor = 11854022
   Case Is > 20: cor = 15189684
   Case Is > 0: cor = 65535
   Case Else: cor = 16777215
  End Select
  On Error Resume Next
  Sheets("Mapa").Shapes(Cells(Target.Row, 8)).Fill.ForeColor.RGB = cor
End Sub

funcionamento - na planilha Vendas, ao alterar manualmente o conteúdo de alguma célula na coluna D ou na coluna H, o código irá pintar no mapa a UF correspondente à alteração conforme as cores da imagem abaixo.

[Yskhadar]

Entendi.

Aí posso desonsiderar macro ColorirMapa, certo?

Outra pergunta: como faço para mudar as cores que você determinou? Como sei o código da cor?

Claro que funcionou, né?

 

[Visitante]

57 minutos atrás, Yskhadar disse:

Aí posso desonsiderar macro ColorirMapa, certo?

Sim. O código que passei faz o trabalho completo.

 

 ... como faço para mudar as cores que você determinou? Como sei o código da cor?

Pinte uma célula com a cor desejada, mantenha a seleção e rode o código abaixo. O número da cor será inserido na célula da direita, aí poderá copiar direto da célula e colar no código.

 

 

Sub NúmeroDaCor()
 ActiveCell.Offset(, 1).Value = ActiveCell.Interior.Color
End Sub

 

[Yskhadar]

Desculpe a demora tive uns contratempos...

Perfeito Osvaldo, muitíssimo obrigado!