VBA counting colors, errors
 

Sorry, people need Extra Sensory perception when i post. Module is as below:

Function CountCol(SumRange As Range, intColor As Integer) As Integer
Dim i As Integer
Dim Cell As Range

Set SumRange = SumRange.SpecialCells(xlCellTypeAllFormatCondition s)

For Each Cell In SumRange
If Cell.Interior.ColorIndex = intColor Then
i = i + 1
End If

Next Cell

CountCol = i

End Function


"Bob Phillips" wrote:

Which module?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"TheRook" wrote in message
...
I am currently having 2 problems.
1. If an additional line in inserted with a specific color the count does
not automatically refresh. Can a loop be put in?

2. If I delete a row the function reports NAME#. Can something be added
to
the module to exclude deleted rows?

regards

VBA counting colors, errors
 

Changing a cell colour does not automatically trigger the calculate event,
so it will not fire your UDF. The best thing to do is to force a recalc by
ALt-F9 after changing a colour.

as to the second problem, this is what I wanted to see the code for, but I
am afraid I do not replicate the problem.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"TheRook" wrote in message
...
Sorry, people need Extra Sensory perception when i post. Module is as
below:

Function CountCol(SumRange As Range, intColor As Integer) As Integer
Dim i As Integer
Dim Cell As Range

Set SumRange = SumRange.SpecialCells(xlCellTypeAllFormatCondition s)

For Each Cell In SumRange
If Cell.Interior.ColorIndex = intColor Then
i = i + 1
End If

Next Cell

CountCol = i

End Function


"Bob Phillips" wrote:

Which module?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"TheRook" wrote in message
...
I am currently having 2 problems.
1. If an additional line in inserted with a specific color the count
does
not automatically refresh. Can a loop be put in?

2. If I delete a row the function reports NAME#. Can something be
added
to
the module to exclude deleted rows?

regards