Ask a question »

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

August 2015


[VBA] A function that returns the color of an active MFC






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
For unlimited offline reading, you can download this article for free in PDF format:
Excel-vba-a-function-that-returns-the-color-of-an-active-mfc.pdf

See also

In the same category

Published by jak58.
This document entitled « Excel/VBA - A function that returns the color of an active MFC » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.