Nothing like this or this, please
I want to obtain the ColorIndex and sum it; 4 cells, a range. Then based on what total is I know my next CF for the actual cell.
I tried everything from Ozgrid, MrExcel, Ablebit, StackO, CF for CPEarson was close to the needs. I don't see the sense with INDIRECT INDIRECT(ADDRESS(ROW(), 7)) = 0
So here we have my function seems a little bit korky but its runs two times then returns #VALUE! which have me waiting for days. i.e. [TRUE 172 total 43 43 43 43 ==: TRUE is my ANSWER]
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
Now, the functions next to it:
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
Indeed, requires to know where the Activated Condition is running, but ugly #VALUE! still show's up in the second condition.
The possible retrieval values are: 3 or 43 or -4142 or the sum 4 times or its combination.
There is it. hope you can, I couldn't.
