none
FORMATAÇÃO CONDICIONAL RRS feed

  • 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.

    segunda-feira, 14 de julho de 2014 12:13

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. 

    https://dl.dropboxusercontent.com/u/80811028/Calend%C3%A1rio%20de%20Atividades%20-%20Formata%C3%A7%C3%A3o%20Condicional.xlsx

    Abraços!


    Rafael Kamimura


    segunda-feira, 14 de julho de 2014 13:45
  • 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

    terça-feira, 15 de julho de 2014 15:20
  • 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!

    https://dl.dropboxusercontent.com/u/80811028/Calend%C3%A1rio%20de%20Atividades%20-%20Formata%C3%A7%C3%A3o%20condicional%202.xlsx


    Rafael Kamimura


    segunda-feira, 14 de julho de 2014 18:34

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. 

    https://dl.dropboxusercontent.com/u/80811028/Calend%C3%A1rio%20de%20Atividades%20-%20Formata%C3%A7%C3%A3o%20Condicional.xlsx

    Abraços!


    Rafael Kamimura


    segunda-feira, 14 de julho de 2014 13:45
  • 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.

    segunda-feira, 14 de julho de 2014 16:30
  • 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

    segunda-feira, 14 de julho de 2014 18:21
  • 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!

    https://dl.dropboxusercontent.com/u/80811028/Calend%C3%A1rio%20de%20Atividades%20-%20Formata%C3%A7%C3%A3o%20condicional%202.xlsx


    Rafael Kamimura


    segunda-feira, 14 de julho de 2014 18:34
  • 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

    --

    segunda-feira, 14 de julho de 2014 19:48
  • 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...

    

    segunda-feira, 14 de julho de 2014 19:56
  • Olá Claiton!

    Você pode disponibilizar um modelo da sua planilha para tentar uma solução já baseado no seu layout?


    Rafael Kamimura

    segunda-feira, 14 de julho de 2014 20:04
  • Rafael segue o link da planilha..

    https://www.sendspace.com/file/gak77r

    segunda-feira, 14 de julho de 2014 20:15
  • 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
    segunda-feira, 14 de julho de 2014 21:18
  • 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

    terça-feira, 15 de julho de 2014 13:01
  • 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

    terça-feira, 15 de julho de 2014 15:20
  • Muito obrigado Jhonatan ficou excelente...

    Ainda vou trabalhar nela pois preciso aplicar mais um controle de data de montagem.

    Muito Obrigado pela força.

    terça-feira, 15 de julho de 2014 19:46
  • 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.

    quarta-feira, 29 de outubro de 2014 19:09