The following VB module calculates the # of cells that have been colored.
It works perfectly if i manually color a cell but if i have conditionnal formatting applied to color a cell it doesn`t count it.
If anybody can find the problem it would be great.
PS the code was taken from and older link in an older post.
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
''''''''''''''''''''''''''''''''''''''
'Sums or counts cells based on a specified fill color.
'''''''''''''''''''''''''''''''''''''''
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function
'
'Examples:
'=ColorFunction($C$1,$A$1:$A$12,TRUE) to SUM the values in range of cells $A$1:$A$12 that have the same fill color as cell $C$1. The reason it will SUM in this example is because we have used TRUE as the last argument for the custom function.
'
'To COUNT these cells that have the same fill color as cell $C$1 you could use: =ColorFunction($C$1,$A$1:$A$12,FALSE) or =ColorFunction($C$1,$A$1:$A$12) by omitting the last argument our function will automatically default to using FALSE.
'
'You will need to recalculate the whole workbook for the function have the correct value appear.
'
It works perfectly if i manually color a cell but if i have conditionnal formatting applied to color a cell it doesn`t count it.
If anybody can find the problem it would be great.
PS the code was taken from and older link in an older post.
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
''''''''''''''''''''''''''''''''''''''
'Sums or counts cells based on a specified fill color.
'''''''''''''''''''''''''''''''''''''''
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function
'
'Examples:
'=ColorFunction($C$1,$A$1:$A$12,TRUE) to SUM the values in range of cells $A$1:$A$12 that have the same fill color as cell $C$1. The reason it will SUM in this example is because we have used TRUE as the last argument for the custom function.
'
'To COUNT these cells that have the same fill color as cell $C$1 you could use: =ColorFunction($C$1,$A$1:$A$12,FALSE) or =ColorFunction($C$1,$A$1:$A$12) by omitting the last argument our function will automatically default to using FALSE.
'
'You will need to recalculate the whole workbook for the function have the correct value appear.
'