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.

You have Option explicit turned on, that means you need to dimension
greenCells as Long as you did with redCells
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Whois Clinton" wrote:

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 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" wrote in message
...
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.

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.

"Whois Clinton" wrote:

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.