counting cell values and storing in a varible for later use using vba
<p>Can someone point me in the right direction, I am not sure how best to approach this so that is solves my problem and does what I need it to do.</p>

<p>I have an excel document there is three column which hold the data of either Green, Amber and Red.  For each row they could have all the same values or different, the over all value for each row would be: -  if one or more of the three values is Red the over all value is red, if there is no red and one or more of the values is Amber the over all is amber if neither of these values are there then it will be green.  I need to count the number of the overall value fro each color but in a specific cell range.  I have some script which will find the first row number and the last row number for the range I need to do the search on: -</p>

<pre><code>mediumStartRow = Range("H:H").Find(what:="Medium", after:=Range("H21")).Row
mediumEndRow = Range("H:H").Find(what:="Medium", after:=Range("H21"), searchdirection:=xlPrevious).Row
</code></pre>

<p>I am not sure of the best why to tackle this problem.  Can anyone point me in the right direction.</p>

<p>I have tried something like this: -</p>

<pre><code>With Range("AS:AU" &amp; mediumStartRow("AS:AU" &amp; mediumEndRow))
     .Formula = "=IF(COUNTIF(Range"=Red")&gt;0,"Red",IF(COUNTIF(Range,"=Amber")&gt;0,"Amber","Green"))"
End With
</code></pre>

<p>this is an example of the excel document</p>

<p><a href="https://i.stack.imgur.com/EKefx.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/EKefx.png" alt="sample excel document"></a></p>