Exclude gray figures from sum
 

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

Exclude gray figures from sum
 

Hi,

Have a look at Bob's excellent solution ...

http://www.xldynamic.com/source/xld.ColourCounter.html

HTH

Exclude gray figures from sum
 

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

"Gustaf" wrote:

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

Exclude gray figures from sum
 

.... forgot to tell you the color index for Grey 40% is : 48

HTH

Exclude gray figures from sum
 

On Jan 5, 7:57 pm, Gustaf wrote:
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

Exclude gray figures from sum
 

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

If c.Interior.ColorIndex < IntColour Then

Mike

"Mike H" wrote:

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

"Gustaf" wrote:

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

Exclude gray figures from sum
 

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

Gustaf