Excel count how many cells have fill color

[ElkySS]

I am working an an Excel workbook that uses quite a few Macros to move and manipulate data that in input on a raw data sheet. Once it gets everything moved where it needs to go it runs a short macro that does some conditional formating and hilights cells blue (ColorIndex = 5) if they are over a goal. All of that is working fine but now I need it to count how many are over goal for each manager (I.E. how many have ColorIndex = 5) in the range. There are 30 managers listed across the top so it will have to loop threw each one. I am not the greatest at programing Excel so here is what I have so far.
Here is some of the macro that I am using to do the conditional formating:

Sub Offer()
'This will do the conditional formating for offer
    Sheets("Master (2)").Select
    Range("G7:AL7").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="=$D$7"
    Selection.FormatConditions(1).Font.ColorIndex = 2
    Selection.FormatConditions(1).Interior.ColorIndex = 5
End Sub
Sub MaxClose()
'This will do the conditional formating for Close
    Range("G8:AL8").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="=$D$8"
    Selection.FormatConditions(1).Font.ColorIndex = 2
    Selection.FormatConditions(1).Interior.ColorIndex = 5
End Sub

Once it does that for each stat and each manager I was running this as a user defined formula:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
    Dim rCell As Range
    Dim lCol As Long
    Dim vResult

'Counts cells based on a specified fill color.
  
    lCol = rColor.Interior.ColorIndex

    If SUM = True Then
        For Each rCell In rRange
            If rCell.Interior.ColorIndex = lCol Then
                vResult = WorksheetFunction.SUM(rCell, vResult)
            End If
        Next rCell
    Else
        For Each rCell In rRange
            If rCell.Interior.ColorIndex = lCol Then
                vResult = 1 + vResult
            End If
        Next rCell
    End If

   ColorFunction = vResult
End Function

and this had this placed in the cells where the total will be going:
=ColorFunction($B$37,G$7:G$31) (changing G to match the range for each manager and I have B37 as a hidden cell that also has ColorIndex = 5)

Problem is that the ColorFunction() macro is counting how many have the conditional formating in it and not how many are actually blue so it comes up with 22 each time. Any ideas on how I can make this work? I hope that this is not too wordy and I really could use any help possible.

 

[ThomasLafferty]

Hi! Try changing this:

If rCell.Interior.ColorIndex = lCol Then

To this:

If rCell.Interior.ColorIndex = lCol [!]And rCell.Value >= ReplaceWithGoalValue[/!] Then

Hope this helps...
Tom

Born once die twice; born twice die once.

 

[SkipVought]

If you are shading the cells woth Conditional Formatting, then you can use that logic using CountIF or SUMPRODUCT.

Skip,

_[red][/red]