I finally have access to the full report (5,400+ rows of data). Most o
the data is unimportant to me except for "Column D". Each cell i
column D is in the following format...

Column D
0H:8M
9H:39M
14H:46M ... and so on.

So all these cells are formatted as [hh]"H":mm"M
(Format=Cells=Number tab=Custom)

Adding these cells is "no problem". The obstacle is that there are
different color cells within column D. I was able to get the exact RG
shade or color (I don't know if this could help).

Color.........R.....G.....B
Green.....204...255..204
Yellow....255...255..153
Red........255...128..128

What I want to accomplish is the following:
Select a range in column D of... let's say 200 rows and have code..
using something like an input box

'...Thanks Tom O
On error resume next
set rng = Application.InputBox( _
"Please select range with mouse", type:=8)
On Error goto 0
if not rng is nothing then

Add total time for Green, Yellow and Red cells... which brings anothe
factor to this whole operation... as time exceeds 24 hours Days ar
created. Therefore, adding 200 rows for example might yield a tota
green cells 4D:14H:46M... Its the 4D (D=days) that I didnt take int
consideration.


I would really be gratefull beyong belief, if anyone can come up with
solution.


Larry
VBA Amateu

--
Message posted from http://www.ExcelForum.com

Thinking things tru... you can forget about the D=days factor... I thin
anyone can understand 147H:49M perfectly.

Larry
VBA Amateu

--
Message posted from http://www.ExcelForum.com

Sub CalculateTime()
Range("D1:D200").Select
Selection.NumberFormat = "[h]""H"":mm""M"";@"
Range("D201").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-200]C:R[-1]C)"
Range("D202").Select
End Sub

This little macro adds range D1:D200 but I have no idea how to add th
color cells (green, yellow, red) separatelly

--
Message posted from http://www.ExcelForum.com

Found this on Chip Pearson's site...


Summing The Values Of Cells With A Specific Color

The following function will return the sum of cells in a range tha
have either an Interior (background) or Font of a specified color.
InRange is the range of cells to examine, WhatColorIndex is th
ColorIndex value to count, and OfText indicates whether to return th
ColorIndex of the Font (if True) or the Interior (if False).
Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Double
'
' This function return the SUM of the values of cells in
' InRange with a background color, or if OfText is True a
' font color, equal to WhatColorIndex.
'
Dim Rng As Range
Dim OK As Boolean

Application.Volatile True
For Each Rng In InRange.Cells
If OfText = True Then
OK = (Rng.Font.ColorIndex = WhatColorIndex)
Else
OK = (Rng.Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(Rng.Value) Then
SumByColor = SumByColor + Rng.Value
End If
Next Rng

End Function
You can call this function from a worksheet cell with a formula like
=SUMBYCOLOR(A1:A10,3,FALSE)


How do I integrate this with my code? can any1 help?? :(


Larry
VBA Amateu

--
Message posted from http://www.ExcelForum.com

Column D
0H:8M
9H:39M
14H:46M ... and so on.

these cells DO NOT have any format. I wish to add the values before th
"H" and the values before the "M" and still have them separated by th
":".

for example...
Column D
0H:8M
9H:39M
----------
Total
9H:47M

To complicate things even more,... there are 3 different color cell
within column D. Below you'll see each color property (I don't know i
this could help).

Color.........R.....G.....B
Green.....204...255..204
Yellow....255...255..153
Red........255...128..128

What I want to accomplish is the following:
Select a range in column D of... let's say 200 rows and have code..
using something like an input box add total time for Green, Yellow an
Red cells... Therefore, adding 200 rows for example might yield a tota
green cells 450H:46M.

The following could be a beginning... for selecting range using a
input box.

'...Thanks Tom O
On error resume next
set rng = Application.InputBox( _
"Please select range with mouse", type:=8)
On Error goto 0
if not rng is nothing then

Also, I Found this on Chip Pearson's site...

Summing The Values Of Cells With A Specific Color

The following function will return the sum of cells in a range tha
have either an Interior (background) or Font of a specified color
InRange is the range of cells to examine, WhatColorIndex is th
ColorIndex value to count, and OfText indicates whether to return th
ColorIndex of the Font (if True) or the Interior (if False).
Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Double
'
' This function return the SUM of the values of cells in
' InRange with a background color, or if OfText is True a
' font color, equal to WhatColorIndex.
'
Dim Rng As Range
Dim OK As Boolean

Application.Volatile True
For Each Rng In InRange.Cells
If OfText = True Then
OK = (Rng.Font.ColorIndex = WhatColorIndex)
Else
OK = (Rng.Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(Rng.Value) Then
SumByColor = SumByColor + Rng.Value
End If
Next Rng

End Function
You can call this function from a worksheet cell with a formula like
=SUMBYCOLOR(A1:A10,3,FALSE)


How do I integrate these codes together? can any1 help??

I would really be gratefull beyong belief, if anyone can come up with
solution.


Larry
VBA Amateu

--
Message posted from http://www.ExcelForum.com