Conditional Formatting - Applying Format Based Custom Functions

F

FARAZ QURESHI

A custom function to sum/count cells of a defined color like the following
works well with normal cells but not with a conditional formatted one. How 2
overcome this problem?

Function SumColor(rSumRange As Range, rColor As Range)
Dim rCell As Range
Dim iCol As Integer
Dim vResult
iCol = rColor.Interior.ColorIndex
For Each rCell In rSumRange
If rCell.Interior.ColorIndex = iCol Then
vResult = WorksheetFunction.Sum(rCell) + vResult
End If
Next rCell
SumColor = vResult
End Function
Function CountColor(rSumRange As Range, rColor As Range)
Dim rCell As Range
Dim iCol As Integer
Dim vResult
iCol = rColor.Interior.ColorIndex
For Each rCell In rSumRange
If rCell.Interior.ColorIndex = iCol Then
vResult = vResult + 1
End If
Next rCell
CountColor = vResult
End Function
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Function returns #Name? 5
Average numbers by fill color 5
Average by color VBA 5
Function to Sum colored cells values not working 4
Conditional Formatted numbers add based on color? 2
User Defined Function for Office 2008 2
Sumproduct VBA 2
SumProduct by cell color 2

Top