none
Tem como Definir a Formatacao Condicional com dados Relativos/Variaveis? RRS feed

  • Pergunta

  • Bom dia.

    Tenho uma pasta em que estou pensando em fazer uma planilha de Configuracao para definir que letra ficar com determinada cor.

    Por exemplo na celula A1 coloco a letra "A", e na celula B1 coloco o numero do ColorIndex desejado. Apos configurado, pretendo utilizar esses dados das celulas na Formatacao Condicional. Na formula da Formatacao Condicional, da para colocar o dados da celula da Configuracao como =Y10=Configurar!$A$1, mas depois na formatacao de cores nao consigo colocar dados do ColorIndex definido em B1. Os dados das celulas A1 e B1, mudam conforme a necessidade do usuario.

    Nao teria uma forma de poder mudar a cor conforme desejado?

    Desde ja aradeco.

    Tadao

    sábado, 11 de fevereiro de 2017 04:50

Respostas

  • Fazendo testes, vi que não é possível acessar a propriedade DisplayFormat numa UDF. Logo, você não consegue fazer a função que deseja por esse caminho.

    Tanto é que esta rotina funciona:

    Sub Main()
        Dim myRange As Range, soma As Double
        Dim Intervalo As Range
        Dim SomaCor As Double
        
        Set Intervalo = Range("A1:A3")
        Dim Cor As Range: Set Cor = Range("b1")
        
        Application.Volatile True
        
        For Each myRange In Intervalo
            If myRange.DisplayFormat.Interior.ColorIndex = Cor.Value2 Then
                SomaCor = SomaCor + 1
            End If
        Next myRange 'Especifique o nome da variável de controle no Next
        'Application.Volatile True - Não precisa deste
        
        Debug.Print SomaCor
    End Sub
    


    http://www.ambienteoffice.com.br || Grupo de WhatsApp: https://chat.whatsapp.com/K1uey5Q4yJdKnsgWkVQAZG

    terça-feira, 14 de fevereiro de 2017 08:01
    Moderador

Todas as Respostas

  • Você pode parametrizar sua formatação condicional, mas o processo não seria dinâmico.

    Assim que você estabelecesse como ficaria sua formatação condicional, clicaria num botão para limpar a formatação condicional anterior e criar uma nova. Use o gravador de macros para descobrir como criar uma formatação condicional.


    http://www.ambienteoffice.com.br || Grupo de WhatsApp: https://chat.whatsapp.com/K1uey5Q4yJdKnsgWkVQAZG

    sábado, 11 de fevereiro de 2017 10:49
    Moderador
  • Ola Felipe, obrigado pelo retorno.

    Fiz conforme sua orientacao mas nao saiu nada, nao sei se fiz alguma sequencia indevida mas a Formatacao esta funcionando e na macro so aparece a palavra macro1, conforme mostra a figura 4.

    Mas pesquisando na internet, achei uma rotina do FormatCondition e consegui fazer a formatacao de tal maneira que posso mudar o dado e a cor de fundo como desejei conforme mostra a figura 1 com o codigo da figura 2, mas........quando fui contar a quantidade de celula com a funcao SomaCor que esta na celula E2 com o codigo na figura 3, notei que a cor que aparece na celula geada pela Formatacao Condicional, nao e reconhecido, ou seja se deixar a celula formatada de cor vermelha gerada pela formatacao condicional e rodar a segunda rotina da figura 2, aparece -4142 que e sem cor. Voce sabe porque? Tem como eu poder contar as celulas formatadas com Formatacao Condicional?

    Tadao

    Fig 1

    Fig 2

    'Formatacao Codicional com VBA
    Sub Macro1()
        Dim myCond As FormatCondition
        With Range("A1:A10")
            .FormatConditions.Delete
            Set myCond = .FormatConditions.Add(Type:=xlCellValue, _
                Operator:=xlEqual, Formula1:=Range("C2").Value2)
            myCond.Interior.colorindex = Range("D2").Value2
        End With
    End Sub
    
    
    Sub LerCor()
    
    MsgBox ActiveCell.Interior.colorindex
    
    End Sub


    Fig 3

    Function SomaCor(Intervalo As Range, Cor As Range) As Double
    Dim myRange As Range, soma As Double
    Application.Volatile True
    For Each myRange In Intervalo
    If myRange.Interior.colorindex = Cor.Value2 Then
       SomaCor = SomaCor + 1
    End If
    Next
    Application.Volatile True
    End Function

    Fig 4


    sábado, 11 de fevereiro de 2017 17:11
  • Qual versão do Excel que está usando?

    Ao tentar criar uma formatação condicional no Excel 2016, obtive o seguinte resultado:

        Range("F8:F21").Select
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
            Formula1:="=10"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Font
            .Color = -16383844
            .TintAndShade = 0
        End With
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 13551615
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
    

    É muito difíicl criar uma formatação condicional "do zero", a não ser que você esteja extremamente familiarizado com o modelo do objeto FormatConditions.

    ---

    Para ver a formatação do resultado de uma formatação condicional, experimente acessar o objeto DisplayFormat.

    myRange.DisplayFormat.Interior.ColorIndex


    http://www.ambienteoffice.com.br || Grupo de WhatsApp: https://chat.whatsapp.com/K1uey5Q4yJdKnsgWkVQAZG

    segunda-feira, 13 de fevereiro de 2017 12:00
    Moderador
  • Ola Felipe, obriado pelo retorno.

    O meu Office e de 2013, mas acho que o codigo  que voce gerou deve ser o mesmo.

    Notei que para ver o numero de cor da formatacao precisa usar o codigo que voce escreveu por ultimo, que e o DisplayFormat.

    Na figura 1, com o cursor na celula A5, onde foi apliado a formatacao, com o codigo abaixo, a primmeira MsgBox retorna -4142 e na sunda MasggBox retorna 3,

    Sub colorindex()
    MsgBox ActiveCell.Interior.colorindex
    MsgBox ActiveCell.DisplayFormat.Interior.colorindex
    
    End Sub
    

    Baseado nisso mudei a rotina da figura 3 acressentando o DisplayFormat mas continua dando erro. 

    Function SomaCor(Intervalo As Range, Cor As Range) As Double
    Dim myRange As Range, soma As Double
    Application.Volatile True
    For Each myRange In Intervalo
    If myRange.DisplayFormat.Interior.colorindex = Cor.Value2 Then
       SomaCor = SomaCor + 1
    End If
    Next
    Application.Volatile True
    End Function

    Onde devo ter errado?

    Tadao

    terça-feira, 14 de fevereiro de 2017 01:59
  • Fazendo testes, vi que não é possível acessar a propriedade DisplayFormat numa UDF. Logo, você não consegue fazer a função que deseja por esse caminho.

    Tanto é que esta rotina funciona:

    Sub Main()
        Dim myRange As Range, soma As Double
        Dim Intervalo As Range
        Dim SomaCor As Double
        
        Set Intervalo = Range("A1:A3")
        Dim Cor As Range: Set Cor = Range("b1")
        
        Application.Volatile True
        
        For Each myRange In Intervalo
            If myRange.DisplayFormat.Interior.ColorIndex = Cor.Value2 Then
                SomaCor = SomaCor + 1
            End If
        Next myRange 'Especifique o nome da variável de controle no Next
        'Application.Volatile True - Não precisa deste
        
        Debug.Print SomaCor
    End Sub
    


    http://www.ambienteoffice.com.br || Grupo de WhatsApp: https://chat.whatsapp.com/K1uey5Q4yJdKnsgWkVQAZG

    terça-feira, 14 de fevereiro de 2017 08:01
    Moderador
  • Entao o DisplayFormat nao funciona para UDF, o jeito e usar eventos para possa calcular o numero de celulas formatadas,nao.

    Vou usar a rotina abaixo para contornar a situacao.

    'Soma as Cores do intervalo com Formatacao Condicional dentro do Intervalo apos Change
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim myRange As Range
    Dim intervalo As Range
    Dim SomaCor As Double
    Dim Cor As Integer
    Set intervalo = Range("A5:A20")
    Cor = Range("D6")
    'so conta dados entrenA5 e A20
    If Not Intersect(intervalo, Target) Is Nothing Then
        For Each myRange In intervalo
            'como e formatacao condicional, precisa usar o DisplayFormat
            If myRange.DisplayFormat.Interior.colorindex = Cor Then
                SomaCor = SomaCor + 1
            End If
        Next myRange
    'desativa o evento para nao disparar qdo coloca dados em E6
    Application.EnableEvents = False
    Range("E6") = SomaCor
    'ativa o evento
    Application.EnableEvents = True
    End If
    
    End Sub

    Muito Obrigado pela solucao, Valeu, aprendi muitas coisas nesta postagem.

    Tadao


    • Editado Antonio Tadao kano quinta-feira, 16 de fevereiro de 2017 10:43 Corrigi um bug do codigo
    terça-feira, 14 de fevereiro de 2017 13:41