Purpose of CF function?

[Dr. Demento]

I'm attempting to use Rick Rothstein's color of CF function (http://www.excelfox.com/forum/showthread.php/338-Get-Displayed-Cell-Color-(whether-from-Conditional-Formatting-or-not)#2). However, one limitation is that only a single cell can be evaluated within a range. I'm trying to use a Chip Pearson function (http://www.cpearson.com/Excel/CFColors.htm) to allow multiple cells to be evaluated for their CF conditions. However, I'm running into an issue with the GetStrippedValue function below; more to the point, what is the function of this function? It fails when the CF.function is something simplistic, such as =5.

I realize the code below doesn't represent the entirety of what I'm attempting to do, but I believe that if I can understand the GetStrippedValue sub-function, I can engineer a work-around.

Thanks y'all.

Function ActiveCondition(Rng As Range) As Integer
Dim Ndx As Long
Dim FC As FormatCondition
Dim Temp As Variant
Dim Temp2 As Variant

If Rng.FormatConditions.Count = 0 Then
     ActiveCondition = 0
Else
     For Ndx = 1 To Rng.FormatConditions.Count
         Set FC = Rng.FormatConditions(Ndx)
         Select Case FC.Type
             CasexlCellValue
             Select CaseFC.Operator
                Case xlBetween
                    Temp = GetStrippedValue(FC.Formula1)
                    Temp2 = GetStrippedValue(FC.Formula2)
                    If IsNumeric(Temp) Then
                       If CDbl(Rng.Value) >= CDbl(FC.Formula1) And _
                           CDbl(Rng.Value) <= CDbl(FC.Formula2) Then
                           ActiveCondition = Ndx
                           Exit Function
                       End If
                   Else
                      If Rng.Value >= Temp And _
                         Rng.Value <= Temp2 Then
                         ActiveCondition = Ndx
                         Exit Function
                      End If
                   End If

                Case xlGreater
                    Temp = GetStrippedValue(FC.Formula1)
                    If IsNumeric(Temp) Then
                       If CDbl(Rng.Value) > CDbl(FC.Formula1) Then
                          ActiveCondition = Ndx
                          Exit Function
                       End If
                    Else
                       If Rng.Value > Temp Then
                          ActiveCondition = Ndx
                          Exit Function
                       End If
                    End If

                Case xlEqual
                    Temp = GetStrippedValue(FC.Formula1)
                    If IsNumeric(Temp) Then
                       If CDbl(Rng.Value) = CDbl(FC.Formula1) Then
                           ActiveCondition = Ndx
                           Exit Function
                       End If
                    Else
                       If Temp = Rng.Value Then
                          ActiveCondition = Ndx
                          Exit Function
                       End If
                    End If

                Case xlGreaterEqual
                    Temp = GetStrippedValue(FC.Formula1)
                    If IsNumeric(Temp) Then
                       If CDbl(Rng.Value) >= CDbl(FC.Formula1) Then
                           ActiveCondition = Ndx
                           Exit Function
                       End If
                    Else
                       If Rng.Value >= Temp Then
                          ActiveCondition = Ndx
                          Exit Function
                       End If
                    End If

                Case xlLess
                    Temp = GetStrippedValue(FC.Formula1)
                    If IsNumeric(Temp) Then
                        If CDbl(Rng.Value) < CDbl(FC.Formula1) Then
                           ActiveCondition = Ndx
                           Exit Function
                        End If
                    Else
                        If Rng.Value < Temp Then
                           ActiveCondition = Ndx
                           Exit Function
                        End If
                    End If

                Case xlLessEqual
                    Temp = GetStrippedValue(FC.Formula1)
                    If IsNumeric(Temp) Then
                       If CDbl(Rng.Value) <= CDbl(FC.Formula1) Then
                          ActiveCondition = Ndx
                          Exit Function
                       End If
                    Else
                       If Rng.Value <= Temp Then
                          ActiveCondition = Ndx
                          Exit Function
                       End If
                    End If

                Case xlNotEqual 
                    Temp = GetStrippedValue(FC.Formula1)
                    If IsNumeric(Temp) Then
                       If CDbl(Rng.Value) <> CDbl(FC.Formula1) Then
                          ActiveCondition = Ndx
                          Exit Function
                       End If
                    Else
                       If Temp <> Rng.Value Then
                          ActiveCondition = Ndx
                          Exit Function
                       End If
                    End If

               Case xlNotBetween
                    Temp = GetStrippedValue(FC.Formula1)
                    Temp2 = GetStrippedValue(FC.Formula2)
                    If IsNumeric(Temp) Then
                       If Not (CDbl(Rng.Value) <= CDbl(FC.Formula1)) And _
                          (CDbl(Rng.Value) >= CDbl(FC.Formula2)) Then
                          ActiveCondition = Ndx
                          Exit Function
                       End If
                    Else
                       If Not Rng.Value <= Temp And _
                          Rng.Value >= Temp2 Then
                          ActiveCondition = Ndx
                          Exit Function
                       End If
                    End If
              
               Case Else
                    Debug.Print "UNKNOWN OPERATOR"
            End Select

         Case xlExpression
             If Application.Evaluate(FC.Formula1) Then
               ActiveCondition = Ndx
               Exit Function
             End If

         Case Else
             Debug.Print"UNKNOWN TYPE"
        End Select

     Next Ndx

End If

ActiveCondition = 0

End Function
  
Function GetStrippedValue(CF AsString) As String
     Dim Temp As String
     If InStr(1, CF, "=", vbTextCompare) Then
        Temp = Mid(CF, 3, Len(CF) - 3)
        If Left(Temp, 1) = "=" Then
            Temp = Mid(Temp, 2)
        End If
     Else
        Temp = CF
     End If
     GetStrippedValue = Temp
End Function

As a somewhat related aside for my project, can someone educate me on what "Application.Evaluate(FC.Formula1)" in Chip's function does? Rick's function uses

Cell.Value >= Evaluate(.Formula1)

which is why only one cell at a time can be evaluated, but by using Chip's function, I can loop thru each cell in a range for each applied format condition. However, I'm unclear as to what this line does exactly.

 

[James006]

Hello,

Evaluate allows to calculate the result of a given formula ... :wink:

When it comes to dealing with the CF conditions .. you should explain your overall objective ...

 

[Dr. Demento]

James,

My overall CF goal is to log the details of all CF in a workbook. Rick's function provides the font and fill colors. The main sub loops thru each page, capturing xlCellTypeAllFormatConditions, then reviewing each format condition. However, as I looked more closely, I couldn't get Rick's function to output the appropriate font/fill, especially when more than one CF is applied to the same range. Instead, I decided to use the native Excel object.AppliesTo.FormatConditions(i).Font.Color. So, I guess that kills that part of my question.

Regarding EVALUATE - why can I not see the output when I attempt to Debug.Print the Evaluate test? It gives me an error even when I attempt to test it like in the code example in my OP.

Thanks for your time/response.