Color Count Macro discrepency

[Whois Clinton]

Hi all,

I am running macros to count the number of various colored cells in a range.
Some are merged cells counted as one. I have a successful macro to count
red and yellow. However, when I change to blue or green there is an error.

Here is the successful macro counting red cells:

Option Explicit
Sub zxVisualRed()
Dim c As Range
Dim MyRange As Range
Dim arrRng() As String
Dim redCells As Long
Dim N As Long
Dim M As Long

Set MyRange = Range("H32:K58")
ReDim arrRng(1 To MyRange.Count)

For Each c In MyRange
If c.Interior.ColorIndex = 3 Then
If c.MergeCells Then
N = N + 1
For M = 1 To N
If c.MergeArea.Address = arrRng(M) Then
Exit For
End If
Next
If M > N Then
redCells = redCells + 1 'CAUSING ERROR
arrRng(N) = c.MergeArea.Address
End If
Else
redCells = redCells + 1 'CAUSING ERROR
End If
End If
Next
MsgBox redCells, vbOKOnly, "Visual Red" 'Does NOT cause error to chang
MsgBox text Red to green
Set c = Nothing
Set MyRange = Nothing
End Sub


When updating to green I change the color index number. That alone works
but when I update the CAUSING ERROR areas to "greencells" the g is
automatically capitalized and the error reads.
"Compile Error : Variable not defined" Highlighting the indicated areas
above. I was able to move from red to yellow doing this method but any other
color is ending in the same error.

Can I just leave the redcell text changing the colorindex alone even though
the text is incorrect?

Thansk in advance for any tips.

 

[John Bundy]

You have Option explicit turned on, that means you need to dimension
greenCells as Long as you did with redCells

 

[Peter T]

John has explained why you get the compile error.

If there are many merged areas involved the following should be a bit
quicker

For Each c In MyRange
If c.Interior.ColorIndex = 3 Then
If c.MergeCells Then
If c.Address = c.MergeArea(1).Address Then
redCells = redCells + 1
End If
Else
redCells = redCells + 1
End If
End If
Next

Regards,
Peter T

 

[Whois Clinton]

Thanks to both of you. Not sure how I missed that text. So obvious once you
are not in it. Thanks also for the shorter version, however this is short
ranges at a time so the result is already immediate.