title: counting conditionally formatted cells in excel
body: <p>Thank you very much for looking into my issue.</p>
  
  <p>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).</p>
  
  <p>I have attempted to write a VB Macro to count the cells. The function is called with <code>=CountRed(Range:Range)</code>, so in my previous example cell H8 <code>=CountRed(H11:H21)</code>.</p>
  
  <p>The problem I have is that the macro does not return the <strong>correct number</strong> 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 <code>Application.Volatile</code> and <code>Application.Volatile(True)</code> doesn't change a thing.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>All formatted cells are the same shade of red (22). </p>
  
  <p>Thank you again for your help!</p>