# Tweaking VB code

#### STEMALO

##### Board Regular
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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### VoG

##### Legend
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.

#### Norie

##### Well-known Member
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.

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.

#### STEMALO

##### Board Regular
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.

Replies
1
Views
410
Replies
7
Views
348
Replies
17
Views
476
Replies
9
Views
332
Replies
3
Views
412

1,191,165
Messages
5,985,036
Members
439,935
Latest member
Monty238

### 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.

### Which adblocker are you using?

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

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