Conditional Formated Cell Color and SUM issues

[titoqan]

Hello, first time poster but long time reader of this forum, it has been a great help!

Okay... I have a column of dates and have colored the rows by days of the week using conditional formating based on formula "=WEEKDAY($B9,1)=7" and applies to a range of "=$B$9:$BR$55"

Next I want to sum cells in a column range that have $BI$9:$BI$55 that have a cell colored based on the conditional formating formula assigned.

I found the following website Conditional Formatting Colors that seems like it should do what I am trying to accomplish. but I get a #NUM! result.

I am running excel2007, and have not done any real VBA coding.

VBA Code:

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(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 ColorIndexOfCF(Rng As Range, _
    Optional OfText As Boolean = False) As Integer
Dim AC As Integer
AC = ActiveCondition(Rng)
If AC = 0 Then
    If OfText = True Then
       ColorIndexOfCF = Rng.Font.ColorIndex
    Else
       ColorIndexOfCF = Rng.Interior.ColorIndex
    End If
Else
    If OfText = True Then
       ColorIndexOfCF = Rng.FormatConditions(AC).Font.ColorIndex
    Else
       ColorIndexOfCF = Rng.FormatConditions(AC).Interior.ColorIndex
    End If
End If
End Function
 
'''''''''''''''''''''''''''''''''''''''

Function ColorOfCF(Rng As Range, Optional OfText As Boolean = False) As Long
Dim AC As Integer
AC = ActiveCondition(Rng)
If AC = 0 Then
    If OfText = True Then
       ColorOfCF = Rng.Font.Color
    Else
       ColorOfCF = Rng.Interior.Color
    End If
Else
    If OfText = True Then
       ColorOfCF = Rng.FormatConditions(AC).Font.Color
    Else
       ColorOfCF = Rng.FormatConditions(AC).Interior.Color
    End If
End If
End Function
'''''''''''''''''''''''''''''''''''''''
Function GetStrippedValue(CF As String) 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
 
'''''''''''''''''''''''''''''''''''''''
Function CountOfCF(InRange As Range, _
    Optional Condition As Integer = -1) As Long
    Dim Count As Long
    Dim Rng As Range
    Dim FCNum As Integer
    For Each Rng In InRange.Cells
        FCNum = ActiveCondition(Rng)
        If FCNum > 0 Then
            If Condition = -1 Or Condition = FCNum Then
                Count = Count + 1
            End If
        End If
    Next Rng
    CountOfCF = Count
End Function
'''''''''''''''''''''''''''''''''''''''
Function SumByCFColorIndex(Rng As Range, CI As Integer) As Double
    Dim R As Range
    Dim Total As Double
    For Each R In Rng.Cells
        If ColorIndexOfCF(R, False) = CI Then
            Total = Total + R.Value
        End If
    Next R
    SumByCFColorIndex = Total
End Function

and In Cell BT10 I use this formula "=SumByCFColorIndex(BI9:BI55,$B$13)"

I should have deleted some of the functions as I am not using them, but this is what I copied and pasted into my file.

Thank you!

 

[StephenCrump]

I think you're looking for:

=SUMPRODUCT(BI9:BI55,--(WEEKDAY(B9:B55,1)=7))

I responded before seeing your Post #6. For Sundays, you'll need:

=SUMPRODUCT(BI9:BI55,--(WEEKDAY(B9:B55)=1)

 

[FDibbins]

Stephen, I think you need WD...2, not 1.

1=Sun to Sat
2=Mon to Sun

 

[titoqan]

I had already caught that

my fromula is "=SUMPRODUCT($BI$9:$BI$55,--(WEEKDAY($B$9:$B$55,1)=1))"
where (Range,1=first day of the week)=1 "Sunday"
where (Range,1=first day of the week)=2 "Monday"
where (Range,1=first day of the week)=3 "Tuesday"... repeated for each day of the week.

Thanks again :D

 

[StephenCrump]

Questions what does "--" do in the formula?

We need to "coerce" the Boolean TRUE/FALSE's into 1's and 0's that SUMPRODUCT can use.

The -- operator (= 2 standard minus signs) is the most efficient and hence most often used way:
-TRUE = -1
So --TRUE = 1
and --FALSE = 0

Similarly, you'll sometimes see 1* or 0+ used to coerce Booleans, because:

1*TRUE = 1 and 1*FALSE=0
0+TRUE = 1 and 0+FALSE=0