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

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


Published 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).