W
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.
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.