A few words of thanks would be greatly appreciated.

Excel/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  
    '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

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
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 (https://ccm.net/).