Exclude gray figures from sum

G

Gustaf

Using Excel 2003. In my budget, I use to mark not-yet-payed costs in
gray color. I use the gray color "Gray -40 %" from Font Color on the
toolbar. I'm trying to make a method that will sum up the costs,
excluding the gray ones. Here's my try:

' Get current column
iColumn = Application.ActiveCell.Column

' Loop through range
For Each c In Range(Cells(22, iColumn), Cells(30, iColumn))
If ... <> ... Then
nSum = nSum + c.Value
End If
Next c

' Print sum
Cells(31, iColumn) = nSum

The problem is that I don't know what to write after If. What property
identifies the font color, and how do I identify the exact gray color
from Font Color on the toolbar?

Gustaf
 
M

Mike H

Hi,

this function will sum by color. To enable summing by different colours you
call the function with
=Colorfunction($A$1)
where A1 is the same color as the cells you want to sum.

Function ColorFunction(MyRange As Range)
iColumn = Application.ActiveCell.Column
Set ColRange = Range(Cells(22, iColumn), Cells(30, iColumn))

Dim c As Range
Dim IntColour As Long
Dim Output
IntColour = MyRange.Interior.ColorIndex
For Each c In ColRange
If c.Interior.ColorIndex = IntColour Then
Output = Output + c.Value
End If
Next
ColorFunction = Output
End Function

Mike
 
K

Ken Johnson

Using Excel 2003. In my budget, I use to mark not-yet-payed costs in
gray color. I use the gray color "Gray -40 %" from Font Color on the
toolbar. I'm trying to make a method that will sum up the costs,
excluding the gray ones. Here's my try:

' Get current column
iColumn = Application.ActiveCell.Column

' Loop through range
For Each c In Range(Cells(22, iColumn), Cells(30, iColumn))
If ... <> ... Then
nSum = nSum + c.Value
End If
Next c

' Print sum
Cells(31, iColumn) = nSum

The problem is that I don't know what to write after If. What property
identifies the font color, and how do I identify the exact gray color
from Font Color on the toolbar?

Gustaf

If c.Font.ColorIndex<>48 Then

When I'm not too sure about a particular property or method I
sometimes just record a macro then check out the recorded code. After
changing the font color of the selected cell to 40% gray this code was
recorded..

With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 48
End With

hence c.Font.ColorIndex<>48

Ken Johnson
 
M

Mike H

I misread your post, you want to exlude a particular color so use this line

If c.Interior.ColorIndex <> IntColour Then

Mike
 
G

Gustaf

Many thanks to all of you helping me with this. Also great advice about
recording a macro to see the properties involved!

Gustaf
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top