title: How do I define an array/collection of all the values within a certain category in Excel VBA?
body: <p>So I have a spreadsheet, with a bunch of names in B51:B220, a few groups in H9;H35, and the corresponding group each name belongs to, in H51:H220. Some of the names in column B are filled red based on a macro I still can’t believe I successfully wrote all by myself. </p>
  
  <p>I’m not sure what exactly I’m doing, but what I’m <em>trying</em> to do is create a count of all the red names in a certain group, and then list that count next to the corresponding group up in the key, in cells F9:F35. </p>
  
  <p>I’m continuing to work on this code even while it’s up here, and editing my post as I progress further.</p>
  
  <p>I know I’m still far from useable code, but here’s what I have so far, other than hopes and dreams:</p>
  
  <pre><code>Sub Team()
  
  Dim TL as Range
  Dim GLA as Collection
  Dim LA as Variant 
  Dim p as Integer 
  Dim t as Integer 
  p = 0 
  
  ‘p is the number of red names 
  ‘t is the row number used for TL key (column H), team size count (column G), and red name count (column F)
  ‘LA should be the individual name that is being tested
  ‘GLA should be an array of each cell from B51:B220 where the value in column H of that same row matches the value of TL
  
  For t = 9 To 35 
  
  TL = Cells(t, 8) 
  
  Set GLA = New Collection
  
  ‘add values to GLA here, as a group of all rows in column B where the same rows in column H = TL 
  ‘I’m guessing that for each value in H51:H220 matching TL, the row number would need to be recorded (as x?)
  ‘and then added to column 2 to make Cells(x, 2), and then each of these individual cells would be stored in GLA 
  
  For Each LA In GLA 
  
  If LA.Interior.ColorIndex = “22” Then 
  
  p = p + 1 
  
  Else 
  
  p = p 
  
  End If 
  
  ‘I’m hoping this will print my p in the appropriate cell but since the code isn’t in running shape yet, i haven’t been able to test it 
  Cells(t, 6) = p
  
  Next LA
  
  Next t 
  
  End Sub 
  </code></pre>
  
  <p><strong>edit</strong>
  I have discovered collection objects. Now I just need help figuring out how to .Add Cells to it based on value. </p>