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
|