Usuário com melhor resposta
FORMATAÇÃO CONDICIONAL

Pergunta
-
Preciso montar um calendário onde vou colorir os dias conforme os periodos de atividades que vou informar exemplo:
Atividade 1 inicio 15/07 final 18/07 - cor verde
Atividade 2 inicio 20/07 final 23/07 - cor azul
atividade 3 inicio 23/07 final 05/08 - cor amarelo
atividade 4 inicio 19/07 final 21/07 - cor laranja
atividade n inicio x final y - cor b
Veja que as atividades podem acabar sendo sobrepostas nas datas, nestes casos as datas sobrepostas deveria ficar em VERMELHO.
Alguem tem um exemplo de VBA para isso, pois na formatação condicional normal não tem como fazer isso.
Respostas
-
Olá Colega!
Você não precisa utilizar VBA para fazer isso. Você pode sim fazer uso da formatação condicional para criar este tipo de controle, inclusive com a formatação em vermelho para datas sobrepostas.
Para você entender melhor, estou postando um modelo que criei agora que está incompleto, e você precisará terminar conforme sua preferência. Eu gostei da ideia da planilha e posteriormente farei uma para meu uso pessoal também e posso postar ela aqui quanto finalizar.
Uma breve explicação de como fazer o uso da formatação condicional neste caso:
1) Gere a tabela de atividades com as colunas de data inicial e data final, que servirão de referencia para montar as fórmulas personalizadas na opção de formatação condicional.
2) Crie os calendários dos meses utilizando a formatação personalizada das células como "d" somente para que a célula contenha a data, mas no rótulo apareça semente o número do dia. Para fazer isso, selecione o intervalo, botão direito, formatar células, na aba "Números" escolha a opção personalizado na lista de categorias e no campo "Tipo:" digite a letra d.
3) Selecione o intervalo do seu calendário faça a formatação condicional para cada atividade, utilizando fórmula personalizada, que ficarão como este modelo de fórmula:
=E("primeira célula do intervalo selecionado">="Data inicial da Atividade X";"primeira célula do intervalo selecionado"<="Data final da Atividade X")
Exemplo, =E(A4>=$D$17;A4<=$F$17)
Observe a posição dos cifrões, pois a célula do intervalo tem que ficar totalmente relativa e a célula das datas finais e iniciais tem que ficar fixas na data indicada na sua tabela de atividades. Essa etapa será trabalhosa, pois tem que criar uma formatação para cada atividade definida na sua tabela.
4) Agora para definir a formatação em vermelho para as datas sobrepostas, será necessário construir uma tabela de apoio para listar todas as datas contidas em cada atividade e então utilizar essa tabela como referência para criar uma fórmula na formatação condicional, que contará cada data do seu calendário e se ela aparecer mais de uma vez na sua tabela de apoio, então significa que ela está sobreposta e basta formatar de vermelho. Importante destacar que esta regra deve ficar na primeira posição entre todas as regras de formatação condicional.
Observe no meu arquivo que a visualização na planilha de apoio ("Chamei de detalhamento") também fica muito interessante. Baixe o arquivo para entender melhor e termine ele conforme as orientações. Qualquer dúvida pergunte. Vou tentar concluir a minha versão essa semana e então disponibilizarei o arquivo.
Rafael Kamimura
- Editado Rafael Kamimura segunda-feira, 14 de julho de 2014 13:46
- Sugerido como Resposta Rafael Kamimura segunda-feira, 14 de julho de 2014 13:47
- Marcado como Resposta Felipe Costa GualbertoMVP, Moderator sábado, 15 de novembro de 2014 10:56
-
Boa Tarde Claiton,
Altere o código anterior pelo código abaixo.
Fiz de modo que ele processa todo o calendário, sempre que há uma alteração de data final (Coluna H).
Dessa forma, pode ser que fique menos rápido pela quantidade de tarefas que terão de ser processadas.
Private Sub Worksheet_Change(ByVal Target As Range)
'valida se foi editada a coluna h
If Target.Column = 8 And Target.Row >= 9 Then
'colore todo calendário de branco
For Each c In Range("n9:au32")
c.Interior.Color = vbWhite
Next
'percorre da linha 9 até 1000
For i = 9 To 1000
'testa se existe data ini e data fim
If IsDate(Cells(i, "g").Value) And IsDate(Cells(i, "h").Value) Then
'Intervalo do calendário
For Each c In Range("n9:au32")
' Teste de Data
If IsDate(c.Value) Then
' Teste de intervalo
If c.Value >= Cells(i, "g").Value And c.Value <= Cells(i, "h").Value Then
' Testa se já está preenchida
If c.Interior.Color = vbWhite Then
' Atribuição da cor
c.Interior.Color = Cells(i, "a").Interior.Color
Else
c.Interior.Color = vbRed
End If
End If
End If
Next
End If
Next i
End If
End Sub
- Editado Jhonatan Franklin terça-feira, 15 de julho de 2014 15:21
- Sugerido como Resposta Jhonatan Franklin quarta-feira, 16 de julho de 2014 12:54
- Marcado como Resposta Felipe Costa GualbertoMVP, Moderator sábado, 15 de novembro de 2014 10:56
-
Não entendi totalmente a sua necessidade e a razão de não conseguir aplicar os conceitos que passei.
Eu fiz já um modelo mais avançado do calendário que estava em mente para desenvolver. Você conseguirá trabalhando com 10 atividades no calendário e se houverem dias sobrepostos irá preencher de vermelho em 3 escalas, conforme quantidade de atividades sobrepostas. Na visão detalhada também dará para ver as atividades e no dia ficará vermelho no caso de datas sobrepostas. Tudo foi feito com base em fórmulas e formatações condicionais e está totalmente dinâmico, você pode alterar as datas no momento que quiser que refletirá automaticamente no calendário. E nada impede de aumentar a quantidade de atividades, basta adicionar nas tabelas e criar a formatação condicional.
Veja se assim não atende sua necessidade.
Abraços!
Rafael Kamimura
- Editado Rafael Kamimura segunda-feira, 14 de julho de 2014 18:38
- Marcado como Resposta Felipe Costa GualbertoMVP, Moderator sábado, 15 de novembro de 2014 10:56
Todas as Respostas
-
Olá Colega!
Você não precisa utilizar VBA para fazer isso. Você pode sim fazer uso da formatação condicional para criar este tipo de controle, inclusive com a formatação em vermelho para datas sobrepostas.
Para você entender melhor, estou postando um modelo que criei agora que está incompleto, e você precisará terminar conforme sua preferência. Eu gostei da ideia da planilha e posteriormente farei uma para meu uso pessoal também e posso postar ela aqui quanto finalizar.
Uma breve explicação de como fazer o uso da formatação condicional neste caso:
1) Gere a tabela de atividades com as colunas de data inicial e data final, que servirão de referencia para montar as fórmulas personalizadas na opção de formatação condicional.
2) Crie os calendários dos meses utilizando a formatação personalizada das células como "d" somente para que a célula contenha a data, mas no rótulo apareça semente o número do dia. Para fazer isso, selecione o intervalo, botão direito, formatar células, na aba "Números" escolha a opção personalizado na lista de categorias e no campo "Tipo:" digite a letra d.
3) Selecione o intervalo do seu calendário faça a formatação condicional para cada atividade, utilizando fórmula personalizada, que ficarão como este modelo de fórmula:
=E("primeira célula do intervalo selecionado">="Data inicial da Atividade X";"primeira célula do intervalo selecionado"<="Data final da Atividade X")
Exemplo, =E(A4>=$D$17;A4<=$F$17)
Observe a posição dos cifrões, pois a célula do intervalo tem que ficar totalmente relativa e a célula das datas finais e iniciais tem que ficar fixas na data indicada na sua tabela de atividades. Essa etapa será trabalhosa, pois tem que criar uma formatação para cada atividade definida na sua tabela.
4) Agora para definir a formatação em vermelho para as datas sobrepostas, será necessário construir uma tabela de apoio para listar todas as datas contidas em cada atividade e então utilizar essa tabela como referência para criar uma fórmula na formatação condicional, que contará cada data do seu calendário e se ela aparecer mais de uma vez na sua tabela de apoio, então significa que ela está sobreposta e basta formatar de vermelho. Importante destacar que esta regra deve ficar na primeira posição entre todas as regras de formatação condicional.
Observe no meu arquivo que a visualização na planilha de apoio ("Chamei de detalhamento") também fica muito interessante. Baixe o arquivo para entender melhor e termine ele conforme as orientações. Qualquer dúvida pergunte. Vou tentar concluir a minha versão essa semana e então disponibilizarei o arquivo.
Rafael Kamimura
- Editado Rafael Kamimura segunda-feira, 14 de julho de 2014 13:46
- Sugerido como Resposta Rafael Kamimura segunda-feira, 14 de julho de 2014 13:47
- Marcado como Resposta Felipe Costa GualbertoMVP, Moderator sábado, 15 de novembro de 2014 10:56
-
Ola Rafael...obrigado pela resposta...mas acho dessa forma não vai funcionar...porque tenho os calendários mensais do ano todo...e as tarefas atividades são listadas fora da sequência cronológica de dias, ou seja, elas podem ser marcadas com antecedência...e elas precisam verificar todos os meses do ano, para colocar os dias no mês correspondente.
E pelo que sei na formatação condicional normal, ela funciona somente na ordem que foi montada e existe um limite de condições, no meu caso vou ter várias tarefas cadastradas para diversos período do ano de forma aleatória.
Então acho que teria que ser uma solução VBA... mas não encontrei nada ainda.
Mas obrigado...ou se tiver outra sugestão.
-
Boa Tarde Claiton,
Veja se o exemplo compartilhado pode lhe ajudar a chegar a sua solução.
No arquivo, a celula b2 é definida para o inicio, celula b3 para o fim e celula b4 escolha da cor, de acordo com as cores de preenchimento do excel.
Após preenchimento, execute a rotina colorir calendário para o devido preenchimento.
https://www.dropbox.com/s/9m276bfjjktl58u/Calendario.xlsm
-
Não entendi totalmente a sua necessidade e a razão de não conseguir aplicar os conceitos que passei.
Eu fiz já um modelo mais avançado do calendário que estava em mente para desenvolver. Você conseguirá trabalhando com 10 atividades no calendário e se houverem dias sobrepostos irá preencher de vermelho em 3 escalas, conforme quantidade de atividades sobrepostas. Na visão detalhada também dará para ver as atividades e no dia ficará vermelho no caso de datas sobrepostas. Tudo foi feito com base em fórmulas e formatações condicionais e está totalmente dinâmico, você pode alterar as datas no momento que quiser que refletirá automaticamente no calendário. E nada impede de aumentar a quantidade de atividades, basta adicionar nas tabelas e criar a formatação condicional.
Veja se assim não atende sua necessidade.
Abraços!
Rafael Kamimura
- Editado Rafael Kamimura segunda-feira, 14 de julho de 2014 18:38
- Marcado como Resposta Felipe Costa GualbertoMVP, Moderator sábado, 15 de novembro de 2014 10:56
-
Ola Jhonatan, obrigado pelo seu exemplo é quase isso que preciso, porém no meu caso vou ter uma planilha com os calendários do ano todo e diversas atividades em períodos de data diferentes cada uma delas e o usuário vai criando as atividades e já iria vendo como vai ficar o comprometimento no calendário, eu tentei utilizar a regra abaixo para que aplicasse a formatação no momento da digitação mas não funcionou, sabe me dizer o que fiz errado?
--
' Procedimento executado quando existe alguma alteração no valor da célula
Private Sub Worksheet_Change(ByVal Target As Range)
' Desabilita outros eventos para tornar mais rápido
' Application.EnableEvents = False
' Verifica se a célula está dentro da área escolhida
If Not Intersect(Target, Me.[A1:AT32]) Is Nothing Then
'Intervalo do calendário
For Each c In Range("M9:AT32")
' Teste de Data
If IsDate(c.Value) Then
' Teste de intervalo
If c.Value >= Range("F9").Value And c.Value <= Range("G9").Value Then
' Testa se já está preenchida
If c.Interior.Color = vbWhite Then
' Atribuição da cor
c.Interior.Color = Range("K9").Interior.Color
Else
c.Interior.Color = vbRed
End If
End If
End If
Next
End Sub--
-
Obrigado pela força Rafael, mas utilizando a formatação condicional vai ficar muito complicado de fazer, porque posso ter mais de 150 atividades para distribuir no calendário anual...quase uma a cada 2 dias, fica bem complicado montar isso manualmente na condicional, a não ser que tu tenha outra ideia.
Coloquei um esboço do que tenho que montar...
-
-
-
Boa Noite,
Aproveitando a solicitação do Rafael, coloquei a função dentro do arquivo postado.
A função só é executada após o preenchimento da data final de produção (Coluna H), após o preenchimento colore o intervalo no calendário de acordo com a cor da coluna A.
https://www.dropbox.com/s/bqek1rdxh22n8h8/Calend%C3%A1rio%20de%20Atividades%20-%20Formata%C3%A7%C3%A3o%20Condicional.xlsm -
Ola Jhonatan, muito obrigado... ficou muito boa sua solução...obrigado...
Só lhe pediria mais uma ajuda para montar uma linha de comando para limpar as cores quando as datas forem deletadas das colunas, caso seja informado um período, percebe-se que não atende a ncessidade, então deleta a data digita uma nova, este movimento de deletar a data deve limpar a formatação do período da tarefa.
obrigado
-
Boa Tarde Claiton,
Altere o código anterior pelo código abaixo.
Fiz de modo que ele processa todo o calendário, sempre que há uma alteração de data final (Coluna H).
Dessa forma, pode ser que fique menos rápido pela quantidade de tarefas que terão de ser processadas.
Private Sub Worksheet_Change(ByVal Target As Range)
'valida se foi editada a coluna h
If Target.Column = 8 And Target.Row >= 9 Then
'colore todo calendário de branco
For Each c In Range("n9:au32")
c.Interior.Color = vbWhite
Next
'percorre da linha 9 até 1000
For i = 9 To 1000
'testa se existe data ini e data fim
If IsDate(Cells(i, "g").Value) And IsDate(Cells(i, "h").Value) Then
'Intervalo do calendário
For Each c In Range("n9:au32")
' Teste de Data
If IsDate(c.Value) Then
' Teste de intervalo
If c.Value >= Cells(i, "g").Value And c.Value <= Cells(i, "h").Value Then
' Testa se já está preenchida
If c.Interior.Color = vbWhite Then
' Atribuição da cor
c.Interior.Color = Cells(i, "a").Interior.Color
Else
c.Interior.Color = vbRed
End If
End If
End If
Next
End If
Next i
End If
End Sub
- Editado Jhonatan Franklin terça-feira, 15 de julho de 2014 15:21
- Sugerido como Resposta Jhonatan Franklin quarta-feira, 16 de julho de 2014 12:54
- Marcado como Resposta Felipe Costa GualbertoMVP, Moderator sábado, 15 de novembro de 2014 10:56
-
-
Boa tarde colega,
Preciso de um calendário como o do colega que postou esta pergunta. No entanto preciso montar uma planilha para férias, onde coloque o nome da pessoa, a data inicial e final das férias e que isso apareça com cores no colendário, cada funcionário seria uma cor correspondente. Nos dias em que cruzarem as datas teria que ficar com funco vermelho. Voce pode por favor me ajudar? estou precisando com urgência.
Obrigado.