[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
See also
Knowledge communities.
