Tweaking VB code

STEMALO

Board Regular
Joined
Apr 15, 2008
Messages
93
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.
'
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
The problem is that you can't count conditionally formatted cells like that. You need to count the cells which meet the criterion of the CF, e.g. greater than a given value or whatever.
 
Upvote 0
STEMALO

There is code to count conditional formatting, and you'll probably find it where you found the code you posted.

But believe me it isn't straightforward.:eek:

If you are using conditional formatting then why not do a conditional count based on the critera in the CF?

How practical/easy that would be depends on the criteria.:)
 
Upvote 0
I would love to do a conditionnal count but the problem i have is that the logic needed to analyse the result would be way to hard to create. That is why we are doing this all manually.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top