Sum the values of cells based on fontcolor
 

I have a worksheet and a need to sum cells with a certain font-color.

I have a number of rows with tasks listed. In the coloums i have 12
months and i the cells are workhours per task per month placed
registrered.

A task can be given a status like this :1) not stated = red, 2 started
= orange,
done = green.

I need to have 3 sums to the right of each tast. A sum for the "Not
started" hours (=red), a sum for the "Stated" hours (=orange) and a
sum for the "Done" hours" (=green).

I now i have to use macros and the need to recalculate with F9 is OK.

But figuring out the macro and how to get the result put in the "sum-
cells" is the ?

Anyone?

Sum the values of cells based on fontcolor
 

This should give you what you want:
Function CountByColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long
'
' This function return the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Dim Rng As Range
Application.Volatile True

For Each Rng In InRange.Cells
If OfText = True Then
CountByColor = CountByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
Else
CountByColor = CountByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
End If
Next Rng

End Function

It is set up for the color red right now.
Look here to find numbers/codes associated with different colors:
http://www.mvps.org/dmcritchie/excel/colors.htm


Regards,
Ryan---

--
RyGuy


" wrote:

I have a worksheet and a need to sum cells with a certain font-color.

I have a number of rows with tasks listed. In the coloums i have 12
months and i the cells are workhours per task per month placed
registrered.

A task can be given a status like this :1) not stated = red, 2 started
= orange,
done = green.

I need to have 3 sums to the right of each tast. A sum for the "Not
started" hours (=red), a sum for the "Stated" hours (=orange) and a
sum for the "Done" hours" (=green).

I now i have to use macros and the need to recalculate with F9 is OK.

But figuring out the macro and how to get the result put in the "sum-
cells" is the ?

Anyone?