Excel/VBA - A function that returns the color of an active MFC

January 2017






This function returns the value of the active format in conditional formatting.
With the function below, two values can be returned.
Mode = 0: the value of Interior.ColorIndex
Mode = 1: the value of Interior.Color


With some changes but all other properties can be returned.



Public Function ColorMFC(RG As Range, Optional Mode As Byte = 0) As Variant  
Dim e As Long, i As Byte, LoTest As Boolean  
Dim LoMFC As FormatCondition  
    Application.Volatile  
    'loop depending on condition(s)  
    'if there is no MFC .FormatConditions.Count return 0  
    For i = 1 To RG.FormatConditions.Count  
        Set LoMFC = RG.FormatConditions(i)  
        If LoMFC.Type = xlCellValue Then  
        'test the type of formula entered'  
            Select Case LoMFC.Operator  
            Case xlEqual  
                LoTest = RG = Evaluate(LoMFC.Formula1)  
            Case xlNotEqual  
                LoTest = RG <> Evaluate(LoMFC.Formula1)  
            Case xlGreater  
                LoTest = RG > Evaluate(LoMFC.Formula1)  
            Case xlGreaterEqual  
                LoTest = RG >= Evaluate(LoMFC.Formula1)  
            Case xlLess  
                LoTest = RG < Evaluate(LoMFC.Formula1)  
            Case xlLessEqual  
                LoTest = RG <= Evaluate(LoMFC.Formula1)  
            Case xlNotBetween  
                LoTest = (RG < Evaluate(LoMFC.Formula1) Or RG > Evaluate(LoMFC.Formula2))  
            Case xlBetween  
                LoTest = (RG >= Evaluate(LoMFC.Formula1)) And (RG <= Evaluate(LoMFC.Formula2))  
            End Select  
            If LoTest Then  
                'Add another format if necessary,  
                'Border, font, policy etc..  
                Select Case Mode  
                Case 0  
                    ColorMFC = LoMFC.Interior.ColorIndex  
                Case 1  
                    ColorMFC = LoMFC.Interior.Color  
                End Select  
                Exit Function  
            End If  
        End If  
    Next i  
    ColorMFC = 0  
End Function


The formula:

=ColorMFC(A2) 


The MFC can be with a direct or indirect value (the contents of another cell)
Ex 1 : >1 and <10   
Ex 2 : >=D10 and <=E10

Related


Published by jak58. Latest update on February 3, 2012 at 06:17 AM by jak58.
This document, titled "Excel/VBA - A function that returns the color of an active MFC," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).