MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

User Defined Function didn't work

  Asked By: Adelina    Date: Feb 09    Category: MS Office    Views: 1364

Please check, this function didn't work? It sums all number!

Maybe the problem is in ".interior.colorindex"? . Need some enlightenment here.



Taken from:

Excel Hacks

Author: David Hawley, Raina Hawley

Pubs: O'Reilly

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

Using a bit of code, you can easily SUM or COUNT cells whose fill color was
specified manually.

Every now and then, it's convenient to SUM or COUNT cells that have a specified
fill color that you or another user have set manually, as users often understand
paint colors more readily than named ranges. To do this, first open the workbook
where you want to COUNT or SUM cells by a fill color. Go into the VBE by
selecting Tools Macro Visual Basic Editor (Alt/Option-F11) and then select
Insert Module to insert a standard module. In this module, type the following

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As

Dim rCell As Range

Dim lCol As Long

Dim vResult

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


For Each rCell In rRange

If rCell.Interior.ColorIndex = lCol Then

vResult = 1 + vResult

End If

Next rCell

End If

ColorFunction = vResult

End FunctionNow you can use the custom function ColorFunction in formulas such
as this:

=ColorFunction($C$1,$A$1:$A$12,TRUE)to sum the values in the range of cells
$A$1:$A$12 that have the same fill color as cell $C$1. The function will sum in
this example because you used TRUE as the last argument for the custom function.

To count the cells that have the same fill color as cell $C$1, you can use this:


=ColorFunction($C$1,$A$1:$A$12)By omitting the last argument, the function
automatically defaults to using FALSE as the last argument. Now you easily can
SUM or COUNT cells that have a specified fill color, as shown in Figure 7-5.

Figure 7-5. Using the custom ColorFunction to count by fill color



2 Answers Found

Answer #1    Answered By: Fayina Bonkob     Answered On: Feb 09

The problem is that the function  does not recalculate when you
change the fill color. You can force it to recalculate by selecting
the cell with the formula, pressing F2, then ENTER. I haven't found
a better way, so the function may not be too usefull...

Answer #2    Answered By: Fabian Ferrrari     Answered On: Feb 09

The function  worked for me. (I am using Excel 2003)

When you place =ColorFunction in your cell, there are two parts. The
first part, is the cell which has the background color that you want to
match. In my instance, cell K1 had the appropriate color. The second
part is the range of cells which should be looked at. In my instance,
H1:H1472. The returned value is the number of cells which match the
appropriate color. If you picked on a cell with no background color,
it may look as though all your cells are being returned to you.

Didn't find what you were looking for? Find more on User Defined Function didn't work Or get search suggestion and latest updates.