Try out the modified version..
Function GetCFColorIndex(c As Range) As Variant
Dim intCount As Integer, FC As FormatCondition, blnMatch As Boolean
If c.Count <> 1 Then Exit Function
For intCount = 1 To c.FormatConditions.Count
'Loop through each Contidional Formatting
Set FC = c.FormatConditions(intCount)
Application.Volatile
If FC.Type = 1 Then
'Handle Type1-xlExpression (If 'Cell Value Is')
Select Case FC.Operator
Case xlBetween '1
If c.Value >= GetCFV(FC.Formula1, c) And c.Value _
<= GetCFV(FC.Formula2, c) Then blnMatch = True: Exit For
Case xlNotBetween '2
If c.Value < GetCFV(FC.Formula1, c) Or c.Value _
GetCFV(FC.Formula2, c) Then blnMatch = True: Exit For
Case xlEqual '3
If c.Value = GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlNotEqual '4
If c.Value <> GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlGreater '5
If c.Value > GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlGreaterEqual '6
If c.Value >= GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlLess '7
If c.Value < GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlLessEqual '8
If c.Value <= GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
End Select
Else
'Handle Type2-xlExternal (If 'Formula Is')
If Evaluate(Application.ConvertFormula( _
Application.ConvertFormula(FC.Formula1, xlA1, xlR1C1), _
xlR1C1, xlA1, , c)) Then blnMatch = True: Exit For
End If
Next
If blnMatch Then GetCFColorIndex = FC.Interior.ColorIndex
End Functio
'-------------------------------------------------------------------------------
Function GetCFV(strData As Variant, c As Range)
'Get text string or numeric from CF formula
If IsNumeric(strData) Then
GetCFV = CDbl(strData)
ElseIf InStr(strData, Chr(34)) Then
GetCFV = Mid(strData, 3, Len(strData) - 3)
Else
GetCFV = Range(Mid(Application.ConvertFormula( _
Application.ConvertFormula(strData, xlA1, xlR1C1), _
xlR1C1, xlA1, , c), 2))
End If
End Functio
'-------------------------------------------------------------------------------
If this post helps click Yes