Sum range up to, and after, a certain cell color or value?
<p>I have a spreadsheet that highlights specific types of Data, and I need separate sums for the values above the first Red row, and then another sum for the values below the LAST instance of a red row. I should mention the rows are conditionally formatted, but if I need a helper row to be inserted that is perfectly allowable. For instance, instead of reading the read CF returning TRUE, in column H could be an "x" every time there is a red row, so the sum could sum up to the first "x" and then after the last instance of "x".</p>

<p>The amount of data that is input, and the rows that are highlighted change with each project, so it cannot be a simple cell reference. There are usually more than just two red rows if that matters. Numbers are always in the yellow rows, so the cell colors of the values themselves are irrelevant.</p>

<p>For instance, for the example below, I need the first sum to return 6, and then the second sum to return 4.  (The cells are not merged, it just looks that way due to the formatting that I included in the snip. All values are in column G)</p>

<p><a href="https://i.stack.imgur.com/UDvuy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/UDvuy.png" alt="Color Example"></a></p>

<p><strong>With Colors:</strong></p>

<pre><code>Sub findRedsAndSum()

redCount = 0
For x = 1 To range("b65536").End(xlUp).row 'find last row
    If range("G" &amp; x).value = "CG" Then  'find red cells
        redCount = redCount + 1
    End If
Next x

redCountAgain = 0

For x = 1 To range("b65536").End(xlUp).row 'find last row
    If range("G" &amp; x).value = "CG" And redCountAgain = 0 Then
        range("I" &amp; x - 1).value = sumVar
        sumVar = 0
        redCountAgain = redCountAgain + 1
    ElseIf range("G" &amp; x).DisplayFormat.Interior.ColorIndex = 3 Then
        redCountAgain = redCountAgain + 1
        sumVar = 0
    End If
    If redCountAgain = redCount And range("G" &amp; x).value &lt;&gt; "CG" Then
        sumVar = sumVar + range("G" &amp; x).value
    End If
    If redCountAgain = 0 Then
</code></pre>

<blockquote>
  <p>sumVar = sumVar + range("G" &amp; x).value</p>
</blockquote>

<pre><code>    End If
    If x = range("b65536").End(xlUp).row Then
        range("I" &amp; x + 1).value = sumVar
    End If

Next x


End Sub
</code></pre>

<p>I am getting a "type mismatch" error on the highlighted line.</p>

<p><strong>With Helper Column:</strong></p>

<p>The red cells in Column "G" (where the values are) now are filled with "CG" instead of being blank. That could be moved to column "I" if it needs to be separated away from the added values.</p>

<p>EDIT:
Still having an issue with the provided answers. The wildcard '?' characters in the list are causing my error, but I would like to preserve that feature if possible.</p>

<p>Here is a snip of the criteria:</p>

<p>acadia?realty?trust<br>
agree?realty?corp<br>
alexander?s?inc<br>
alexander?s??inc<br>
alexandria?real?estate?equities?inc  </p>