title: How to get the cell count matching the value and color of the active from CountIF, and loop code equal to cell count found from CountIF in VBA Excel?
body: <p>I have another question of similar nature of my resent questions. I have a function in VBA that finds cells matching the date and cell color of the active cell, after it finds the next matching cell, it goes to corresponding cell in Column H, and highlights it Cyan. This works just what I want it, but I have to click the run macro every time. I want the function to work on all matching cells. I am thinking on using a Do Until loop, found @ <a href="http://www.excel-easy.com/vba/examples/do-until-loop.html" rel="nofollow">http://www.excel-easy.com/vba/examples/do-until-loop.html</a>, but to do this I need to know the number of matching cells to make the loop stop. </p>
  
  <p>My working code:</p>
  
  <pre><code>Sub Test1()
  '
  ' Test1 Macro
  '
  '
  Dim CellColor As Variant
  Dim SearchDate As String, FoundAt As String
  
  
  CellColor = Range("B" &amp; ActiveCell.Row).Interior.Color
  SearchDate = Range("B" &amp; ActiveCell.Row).NumberFormat
  
      Range("B" &amp; ActiveCell.Row).Select
      Application.FindFormat.Clear
      Application.FindFormat.NumberFormat = SearchDate
      Application.FindFormat.Interior.Color = CellColor
  
      Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
          xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False _
          , SearchFormat:=True).Activate
  
  End Sub
  </code></pre>
  
  <p>The loop code I am thinking on using found in the link above:</p>
  
  <pre><code>Dim i As Integer
  i = 1
  
  Do Until i &gt; 6
      Cells(i, 1).Value = 20
      i = i + 1
  Loop
  </code></pre>
  
  <p>I have this code of CountIF:</p>
  
  <pre><code>Dim cellCount As Integer
  cellCount = Application.WorksheetFunction.CountIf(Range("B1:B30"), "7/22/2016") ‘Count matching 
  
  MsgBox cellCount ‘test to see if count works
  </code></pre>
  
  <p>But there are two problems wrong with it. Problem One, I have to manually enter the date “7/22/2016” into the code instead of using the variable “SearchDate.” Problem Two is that it only searches for the date, and not the cell color that filters the dates in my spreadsheet. </p>
  
  <p>So my question is in all of this. How do I get the count of number of dates meeting the date and color value of the active cell, and carry that number over to a variable to be used in a loop?</p>
  
  <p>If there is a more efficient way to do all of this, please tell me. Many thanks in advance!!!</p>
  
  <p>Reference pictures:</p>
  
  <p><a href="http://i.stack.imgur.com/IPN6n.jpg" rel="nofollow">Screenshot showing the spreadsheet of dates and colors.</a></p>