Excel count how many cells have fill color

[ElkySS]

** I will get this right one of these days.

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.

 

[PHV]

Have you tried to play with the SUMIF and COUNTIF worksheet functions instead of counting the colored cells ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ElkySS]

I am not sure how that would really do what I need it to do. There are 22 different stats each having a different goal. Only the stats that are above goal turn bule from the conditional formating. Then I just need it to tell me how many are blue.

 

[SkipVought]

Then use the same logic (formula) that is used in the CF to count the blue. You can probably do it on the sheet rather than in VB Code.

Skip,

_[red][/red]