Ask a question »

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

May 2016

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  
    '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:


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:

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