Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting errors | Excel Worksheet Functions | |||
Counting the number of errors in a worksheet | Excel Worksheet Functions | |||
Counting with a condition disregarding errors | Excel Worksheet Functions | |||
Counting Errors | Excel Worksheet Functions | |||
Counting colors | Excel Discussion (Misc queries) |