Microsoft Excel Functions & Formulas

Use a user-defined function to sum all

shaded cells

This tip shows how to sum all shaded cells. Copy to your

worksheet the values in range A1:A5, as shown in Figure 13-6.

Format two of the cells with the color red and define a special

user-defined function to sum them up.

4

To sum all shaded cells:

1. Press <Alt+F11> to open the Visual Basic Editor.

2. From the Insert menu, click Module.

3. Type the following function:

Function SumColor(Area As Range, Ci As Integer)

Dim sng As Single, rng As Range

For Each rng In Area

If rng.Interior.ColorIndex = Ci Then sng =

sng+rng.Value

Next rng

SumColor = sng

End Function

4. Return to cell C6 of the worksheet and type the ...