none
Formatação Condicional por Colunas RRS feed

  • Pergunta

  • Olá boa tarde,

     Estou precisando de uma fórmula para formatação condicional em meu relatório para colunas.

    Para cada mês selecionado, deverá pintar uma coluna.

    Exemplo: O usuário selecionou a versão V01 correspondente ao mês 1 - Janeiro. Dessa forma a coluna janeiro deverá vir azul. Até aqui tudo bem, dá para fazer uma simples formatação nas células.

    Exemplo 2: O usuário seleciona a versão V02 correspondente ao mês de fevereiro. Dessa forma a coluna janeiro e fevereiro deverão vir em azuis. 

    Neste aqui, não estou acertando a fórmula, pois conforme seleciono as duas colunas e coloco uma fórmula como =E($I$4="V02"), consigo pintar a coluna de fevereiro (devidamente em azul) mas a primeira coluna de janeiro não corresponde em azul também.

    Exemplo 3: O usuário seleciona versao V03 correspondente a março. E deverá pintar as colunas, janeiro, fevereiro e março em azuis. 


    Qual fórmula posso utilizar?

    OBSERVAÇÃO: Quando não selecionado nenhuma versão V00, as cores originais deverão ser branco e laranja conforme mostrado em figuras. 

    Desde já agradeço!!

    quinta-feira, 24 de julho de 2014 19:15

Respostas

  • Bom dia á todos,

     Depois de muitas tentativas, consegui resolver meu problema.

    Eu solucionei com uma fórmula excel mesmo.

    Coloquei um Se e Procv na Versão.

    Dessa forma consegui "pintar" as colunas que correspondem a Versão selecionada no contexto, mantendo NA_VERSAO (Sem versão).

    Agradeço a todos pela ajuda, foi de grande conhecimento.

    Obrigada

    • Marcado como Resposta Thaís Sakamoto quarta-feira, 30 de julho de 2014 13:41
    quarta-feira, 30 de julho de 2014 13:41

Todas as Respostas

  • Olá Thais,

    é bem simples, mas você terá que adicionar uma linha informando que o mês JAN.2015 é referente ao V01...

    segue exemplo:

    quinta-feira, 24 de julho de 2014 19:35
  • Oi Natan,

    Agradeço a sua ajuda.

    Porém, quando eu selecionar Fevereiro (Versão 02 - V02): deverá vir pintado janeiro e fevereiro.

    Quando eu selecionar Março (Versão 03 - V03), deverá vir pintado: janeiro, fevereiro e março.

    Quando eu selecionar Dezembro (Versão 12 - V12) deverá vir pintado: janeiro, fevereiro, março, abril, maio, junho, julho, agosto, setembro, outubro, novembro e dezembro.

    É como se fosse acumulando os meses, conforme selecionarmos a versão.

    quinta-feira, 24 de julho de 2014 20:04
  • Olá mais uma vez Thais!

    Tenho uma solução para você, selecione o intervalo da tabela para aplicar a formatação e insira a seguinte fórmula lá na caixa da formatação condicional:

    =(COL(J7)-9)<=VALOR(DIREITA($I$4;2))

    Só que se você usar essa fórmula você não vai mais poder inserir colunas antes da tabela. Se quiser fazer de uma forma que não tenha esse problema, transforme o cabeçalho da sua tabela como data e não como texto (use 01/01/2015 para janeiro, 01/02/2015 para fevereiro e arrasta para os demais meses e formate as células para aparecer só o mê e ano). E então, a fórmula da formatação condicional vai ser:

    =MÊS(J$6)<=VALOR(DIREITA($I$4;2))

    Vê ai se consegue e qualquer coisa me avisa.


    Rafael Kamimura

    quinta-feira, 24 de julho de 2014 22:52
  • Thaís, acho que isso resolve seu problema.

    Você deve inserir o código no VBA: EstaPasta_de_trabalho.

    Esta Sub também pode ser alterada desta forma: 

    If (Target.Value = "V01") Then

    Da forma acima, não importa qual célula está selecionada, se você inserir os valores: V01, V02,... Irá formatar a seleção.

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    Dim selecao As Range
    
    
    If (Range("I4").Value = "V01") Then
    Range("J7:Z20").Interior.Color = RGB(255, 255, 255)
    Set selecao = ActiveSheet.Range("J7:j20")
    selecao.Interior.Color = RGB(150, 200, 200)
    selecao.Borders.LineStyle = xlGray75
    End If
    
    If (Range("I4").Value = "V02") Then
    Range("J7:Z20").Interior.Color = RGB(255, 255, 255)
    Set selecao = ActiveSheet.Range("J7:l20")
    selecao.Interior.Color = RGB(150, 200, 200)
    selecao.Borders.LineStyle = xlGray75
    End If
    
    If (Range("I4").Value = "V03") Then
    Range("J7:Z20").Interior.Color = RGB(255, 255, 255)
    Set selecao = ActiveSheet.Range("J7:m20")
    selecao.Interior.Color = RGB(150, 200, 200)
    selecao.Borders.LineStyle = xlGray75
    End If
    
    If (Range("I4").Value = "V04") Then
    Range("J7:Z20").Interior.Color = RGB(255, 255, 255)
    Set selecao = ActiveSheet.Range("J7:n20")
    selecao.Interior.Color = RGB(150, 200, 200)
    selecao.Borders.LineStyle = xlGray75
    End If
    
    
    End Sub


    • Editado Supimpa quinta-feira, 24 de julho de 2014 23:44
    quinta-feira, 24 de julho de 2014 23:34
  • Olá Thaís,

    Boa noite. Veja um exemplo pronto a seguir: https://onedrive.live.com/redir?resid=FB206A2D510E0661!22408&authkey=!AOUZj34UcLadHtA&ithint=file%2cxlsx


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    sexta-feira, 25 de julho de 2014 01:46
    Moderador
  • Boa noite,

    Pode utilizar o código abaixo na sua planilha.

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    ' valida se foi editada a celula i5
    If Target.Address = "$I$5" Then
        
        ' colore o intervalo de branco
        For Each C In Range("J8:U20")
            C.Interior.Color = vbWhite
        Next
        
        ' define a última coluna a ser colorida
        Fim = Replace(Target, "V", "") + 9
        
        ' testa se a coluna final é maior que 9
        If Fim > 9 Then
            ' colore o intervalo.
            ' nesse caso a primeira linha é a 8 e a ultima linha é a 20, mas pode ser alterada de acordo com a necessidade
            For Each C In Range(Cells(8, 10), Cells(20, Fim))
                C.Select
                With Selection.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorAccent1
                    .TintAndShade = 0.399975585192419
                    .PatternTintAndShade = 0
                End With
            Next
        
        End If
    End If
    
    End Sub

    Obs: No primeiro for each que colore todo o intervalo de branco, você pode gravar uma macro com sua formatação original (Laranja e Branco) e substituir para que ao colocar o valor 0 já fique no padrão original.

    Att,

    sexta-feira, 25 de julho de 2014 02:05
  • Bom dia a Todos!

    Thais,

    Da pra fazer sem VBA.

    Altera a fórmula da condicional...

    =SUBSTITUIR($D$7;"V";"")>=SUBSTITUIR(D$9;"V";"")

    sexta-feira, 25 de julho de 2014 10:20
  • Olá bom dia,

    Vc trabalha na Seal Sistemas há quanto tempo? Trabalhei na Telecom por três anos e agora estou no magnífico mundo SAP rs

    Voltando a solução.

    Eu tentei fazer pela macro mas não está funcionando/rodando. A cor não muda.

    Veja: Esta é minha planilha

    E este é meu código:

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim selecao As Range


    If (Range("I4").Value = "V01") Then
    Range("I11:T20").Interior.Color = RGB(255, 255, 255)
    Set selecao = ActiveSheet.Range("J11:J20")
    selecao.Interior.Color = RGB(150, 200, 200)
    selecao.Borders.LineStyle = xlGray75
    End If

    If (Range("I4").Value = "V02") Then
    Range("I11:T20").Interior.Color = RGB(255, 255, 255)
    Set selecao = ActiveSheet.Range("J11:K20")
    selecao.Interior.Color = RGB(150, 200, 200)
    selecao.Borders.LineStyle = xlGray75
    End If

    If (Range("I4").Value = "V03") Then
    Range("I11:T20").Interior.Color = RGB(255, 255, 255)
    Set selecao = ActiveSheet.Range("J11:L20")
    selecao.Interior.Color = RGB(150, 200, 200)
    selecao.Borders.LineStyle = xlGray75
    End If

    If (Range("I4").Value = "V04") Then
    Range("I11:T20").Interior.Color = RGB(255, 255, 255)
    Set selecao = ActiveSheet.Range("J11:M20")
    selecao.Interior.Color = RGB(150, 200, 200)
    selecao.Borders.LineStyle = xlGray75
    End If


    End Sub

    Obrigada

    sexta-feira, 25 de julho de 2014 14:16
  • Thais, não tem a necessidade de achar uma solução complicada com VBA. Use formatação condicional mesmo. A fórmula para isso baseado na imagem da sua planilha é:

    =(COL(I7)-COL($I$7)+1)<=VALOR(DIREITA($I$4;2))

    Selecione o intervalo I11:T20 e aplique a formatação condicional por fórmula, utilizando essa expressão. Garanto que vai funcionar perfeitamente.


    Rafael Kamimura



    sexta-feira, 25 de julho de 2014 14:26
  • Eu vi que na imagem está diferente a referência do V0X com relação ao mês, pois na questão inicial foi exposto que V01 seria janeiro e na imagem acima leva a entender que V01 é fevereiro e V00 é janeiro. Se for isso mesmo, tire o "+1" da fórmula que passei:

    =(COL(I7)-COL($I$7))<=VALOR(DIREITA($I$4;2))


    Rafael Kamimura

    sexta-feira, 25 de julho de 2014 14:38
  • Olé Felipe,

     Testei aqui conforme o seu exemplo.

    Porém não funciona para mim. Eu trabalho com um suprimento EPM no excel.

    No contexto a fórmula funciona.

    Mas tenho que "prender" (fazer a mesma formatação condicional) no formato que esta ferramenta EPM utiliza.

    Porém como não consigo fazer referência em diferentes planilhas, ao atualizar ou selecionar uma versão: a formatação condicional se perde e não funciona.

    Grata


    sexta-feira, 25 de julho de 2014 14:38
  • Boa Tarde Thaís,

    Modifiquei o código de acordo com o exemplo da sua planilha, veja se lhe ajuda.

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    ' valida se foi editada a celula i4
    If Target.Address = "$I$4" Then
        
        ' colore o intervalo de branco
        ' grave uma macro com sua formatação original e substitua esse trecho
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        For Each C In Range("I11:T20")
            C.Interior.Color = vbWhite
        Next
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        
        ' define a última coluna a ser colorida
        Fim = Replace(Target, "V", "") + 8
        
        ' testa se a coluna final é maior que 8
        If Fim > 8 Then
            ' colore o intervalo.
            ' nesse caso a primeira linha é a 11 e a ultima linha é a 20, mas pode ser alterada de acordo com a necessidade
            For Each C In Range(Cells(11, 9), Cells(20, Fim))
                C.Select
                With Selection.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorAccent1
                    .TintAndShade = 0.399975585192419
                    .PatternTintAndShade = 0
                End With
            Next
        
        End If
    End If
    
    End Sub
    

    • Marcado como Resposta Thaís Sakamoto sexta-feira, 25 de julho de 2014 16:55
    • Não Marcado como Resposta Thaís Sakamoto sexta-feira, 25 de julho de 2014 16:58
    sexta-feira, 25 de julho de 2014 15:46
  • Oi Jhonatan,

     Está rodando e funcionando conforme versão.

     Porém perdi a formatação original.

    Células brancas na conta de RECEITA BRUTA

    E laranja nas outras contas 

    sexta-feira, 25 de julho de 2014 17:02
  • Oi Thais,

    Teste esse código abaixo, ele está formatando igual o último exemplo, mas pelo que ví a sua planilha parece ser maior, caso seja grave uma macro com seu formato padrão e susbstitua pelo trecho comentado (SUBSTITUIR).

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    ' valida se foi editada a celula i4
    If Target.Address = "$I$4" Then
        
        ' colore o intervalo de branco
        ' grave uma macro com sua formatação original e substitua esse trecho
        '''''''''(SUBSTITUIR)''''''''''''''''''''''''''''''''''''''''''''''''
        Range("I11:T11").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Range("I12:T20").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
        ''''''''''(SUBSTITUIR)'''''''''''''''''''''''''''''''''''''''''''''
        
        ' define a última coluna a ser colorida
        Fim = Replace(Format(Target, ">"), "V", "") + 8
        
        ' testa se a coluna final é maior que 8
        If Fim > 8 Then
            ' colore o intervalo.
            ' nesse caso a primeira linha é a 11 e a ultima linha é a 20, mas pode ser alterada de acordo com a necessidade
            For Each C In Range(Cells(11, 9), Cells(20, Fim))
                C.Select
                With Selection.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorAccent1
                    .TintAndShade = 0.399975585192419
                    .PatternTintAndShade = 0
                End With
            Next
        
        End If
    End If
    
    Range("$I$4").Select
    End Sub


    sexta-feira, 25 de julho de 2014 17:13
  • Thaís, não quero menosprezar todas as soluções em VBA que foram passadas aqui, acho que em determinadas situações temos que recorrer a programação.  Entretanto, não faz sentido para mim aplicar uma solução dessa em VBA, ainda mais atrelado ao evento "Change" da planilha, que vai rodar a cada alteração na planilha, que consequentemente pode tornar a sua planilha mais lenta, se você pode facilmente resolver isso com uma só fórmula na ferramenta de formatação condicional.

    Mas é só minha opinião.

    Abraços!


    Rafael Kamimura

    sexta-feira, 25 de julho de 2014 17:45
  • Olá Rafael tudo bem contigo?

    E se tentássemos algo por linha? Para que eu não perca a referência das cores ao atualizar.

    Exemplo:

    

    Porém teria que ter alguma fórmula para pintar por coluna e mês.

    sexta-feira, 25 de julho de 2014 17:54
  • Olá Rafael,

     Posso dizer que estou tentando das duas formas.

    O problema é que estou utilizando um suprimento (ferramenta) do sistema SAP (BPC10NW) no excel para report.

    Este suprimento EPM é bem dinâmico e muitas vezes não aceita fórmulas do excel, pois possui sua própria formatação.

    As vezes tento juntar os dois, tanto excel como BPC (epm), para deixar tanto fixo como dinâmico.

    O problema que estou tendo é que ao formatar o excel pela formatação condicional. Ele funciona sim.

    Mas ao atualizar a ferramenta EPM, ele some. 

    Uma vez consegui colocar a formatação condicional no excel e copiei para a formatação EPM. Mas era uma formatação simples do tipo se for maior ou igual a. Ele não esta aceitando uma fórmula como as que me passou.

    Por isso eu estou tentando acertar a fórmula excel e tentando encaixar no EPM também.

    Assim como estou tentando fazer com que rode pela macro.

    É claro que devo verificar e levar em conta a lentidão do meu relatório.

    Grata

    sexta-feira, 25 de julho de 2014 18:15
  • Olá Thaís.

    Estou há um ano na Seal. Fui para trabalhar como analista Protheus, mas trocaram o sistema para SAP e me mandaram para um projeto de um sistema logístico de VOZ em C#.

    Segue link de um modelo que fiz em VBA e a senha para descompactar a planilha abaixo. Veja se te ajuda. A letra V da célula I4, deve ser digitada em maiúscula, ou pode ser alterado o código colocando um OR.

    http://transfer.cbdnet.com.br/Externo/Download/3442ab66-085e-41bf-9281-9bbaf5cfe045

    1uByWGs10qNh1pKGD0Lk

    Todos os métodos enviados são funcionais, pode ser que este suprimento esteja atrapalhando a execução.


    Felipe Costa GualbertoParabéns novamente por compartilhar seu conhecimento. Tem muita coisa no link que enviou. 

    Abçs.



    • Editado Supimpa sexta-feira, 25 de julho de 2014 22:38
    sexta-feira, 25 de julho de 2014 19:22

  • "O problema que estou tendo é que ao formatar o excel pela formatação condicional. Ele funciona sim.

    Mas ao atualizar a ferramenta EPM, ele some."


    Bom, acredito que este suplemento funciona da seguinte forma: quando você interage com o suplemento, este abre um modelo (que pode estar na rede, um xlt*) e exibe para você como uma nova pasta de trabalho, não salva. Logo, não adianta você alterar a formatação porque toda vez que fizer uma interação, o modelo será recarregado. Neste caso, você tem duas opções para resolver seu problema:

    1 - Recomendado: Entrar em contato com o administrador do suplemento que é responsável por manter o modelo, e fazer as alterações de formatação condicional desejadas nele.

    2 - Mais trabalhoso: criar uma macro em sua pasta de trabalho que adicione essa formatação condicional na planilha. Neste caso, você teria que rodar essa sua macro pessoal toda vez que tirasse um novo relatório, a não ser que criasse um handler para monitorar as pastas de trabalhos abertas na sessão.


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    sexta-feira, 25 de julho de 2014 22:20
    Moderador
  • Bom dia á todos,

     Depois de muitas tentativas, consegui resolver meu problema.

    Eu solucionei com uma fórmula excel mesmo.

    Coloquei um Se e Procv na Versão.

    Dessa forma consegui "pintar" as colunas que correspondem a Versão selecionada no contexto, mantendo NA_VERSAO (Sem versão).

    Agradeço a todos pela ajuda, foi de grande conhecimento.

    Obrigada

    • Marcado como Resposta Thaís Sakamoto quarta-feira, 30 de julho de 2014 13:41
    quarta-feira, 30 de julho de 2014 13:41