Conditional Formatting - Applying Format Based Custom Functions
 

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

--

Best Regards,
FARAZ A. QURESHI

Conditional Formatting - Applying Format Based Custom Functions
 

See this:

http://www.cpearson.com/Excel/CFColors.htm

--
Biff
Microsoft Excel MVP


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

--

Best Regards,
FARAZ A. QURESHI

Conditional Formatting - Applying Format Based Custom Function
 

Great!!!

Thanx Biff!!!

--

Best Regards,
FARAZ A. QURESHI


"T. Valko" wrote:

See this:

http://www.cpearson.com/Excel/CFColors.htm

--
Biff
Microsoft Excel MVP


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

--

Best Regards,
FARAZ A. QURESHI