vba 2013 сума за условно форматиране ColorIndex

Нищо подобно на това или това, моля

Искам да получа ColorIndex и да го сумирам; 4 клетки, диапазон. След това въз основа на общата сума знам следващия си CF за действителната клетка.

Опитах всичко от Ozbit, http://mExcel, A stackoverflow.com/questions/7408899/how-do-i-find-the-fill-colour-value-of-a-conditionally-formatted-cell-in-excel-2">StackO, CF за CPEarson беше близо до нуждите. Не виждам смисъл от INDIRECT INDIRECT(ADDRESS(ROW(), 7)) = 0

И така, тук имаме моята функция изглежда малко измамна, но тя се изпълнява два пъти, след което връща #VALUE! които ме чакат с дни. т.е. [ВЯРНО 172 общо 43 43 43 43 ==: ВЯРНО е моят ОТГОВОР]

Function bgcolor(Rng As Range) As Boolean
 Dim R As Variant
 Dim C As Variant
 Dim AC As Integer
 Dim Total As Double

 AC = ActiveCondition(Rng)

For Each C In Rng.Cells
    For Each R In Rng.Cells
    Cells(R, C).Select

        If Selection.FormatConditions(R).Interior.ColorIndex = 2 Then
            Total = Total + 2
        Else
           If Selection.FormatConditions(R).Interior.ColorIndex = 43 Then
            Total = Total + 43
            Else
            Total = Total + 3
           End If
        End If

    Next R
Next C

Select Case Total
Case Total = -12423
    bgcolor = False
Case Total = -12383
    bgcolor = True
Case Total = -8278
    bgcolor = False
Case Total = -8198
    bgcolor = True
Case Total = -4133
    bgcolor = False
Case Total = -4013
    bgcolor = True
Case Total = 12
    bgcolor = False
Case Total = 52
    bgcolor = False
Case Total = 92
    bgcolor = False
Case Total = 132
    bgcolor = True
Case Total = 172
    bgcolor = True


    End Select

End Function

Сега функциите до него:

 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
         Case xlCellValue
         Select Case FC.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(Temp) 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(Temp) 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(Temp) 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 As String) As String
' http://www.cpearson.com/excel/CFColors.htm
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

Наистина изисква да се знае къде се изпълнява Активираното условие, но грозно #VALUE! все още се показва във второто условие.

Възможните стойности за извличане са: 3 или 43 или -4142 или сумата 4 пъти или нейната комбинация.

Има го. дано можеш, аз не можах.