counting conditionally formatted cells in excel

Question

Thank you very much for looking into my issue.

Basically I've got a dashboard that I have conditionally formatted - depending on what value is returned to that cell will be if the cell turns red or remains white/unshaded. I have a series of rows at the top of the dashboard that need to total the number of red cells under each column (so H8 sums the total # of red cells in the range H10:H21).

I have attempted to write a VB Macro to count the cells. The function is called with =CountRed(Range:Range), so in my previous example cell H8 =CountRed(H11:H21).

The problem I have is that the macro does not return the correct number of red cells. (It will return 5 when there are 3, for example.) I also have no idea why my function isn't dynamic; Using both Application.Volatile and Application.Volatile(True) doesn't change a thing.

Function CountRed(MyRange As Range)
 Dim iCount As Integer
 Application.Volatile
 iCount = 0
 For Each cell In MyRange
    If cell.Interior.ColorIndex = 22 Then
        iCount = iCount + 1
    End If
 Next cell
 CountRed = iCount
End Function

All formatted cells are the same shade of red (22).

Thank you again for your help!

Solution

You probably want to look at the countif function.

http://office.microsoft.com/en-au/excel-help/countif-HP005209029.aspx

You may need to reproduce the conditional formatting logic into the if condition of this function, but that should be pretty straight forward. Post some more information about the sheet & conditional formatting rules if you'd like help with that.