Function GetTime(rng As Range, ci As Long, Start As Boolean)
Dim cell As Range
Dim i As Long
If Start Then
For i = rng.Row To rng.Rows.Count
If rng.Rows(i).Interior.ColorIndex = ci Then
GetTime = TimeSerial(i, 0, 0)
Exit For
End If
Next i
Else
For i = rng.Rows.Count To rng.Row Step -1
If rng.Rows(i).Interior.ColorIndex = ci Then
GetTime = TimeSerial(i, 0, 0)
Exit For
End If
Next i
End If
End Function

Call it like

=GetTime(B1:B10,3,True)

start time for red cells, or

=GetTime(B1:B10,3,False)

for end time

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"darkbearpooh1"
wrote in message
news:darkbearpooh1.23en6n_1140232202.4401@excelfor um-nospam.com...

i am trying to color across a range of cells and i need a formula or
something
to give me a time or a number value based on the colored cells starting
cell
and finishing cell. i already have a macro that counts how many cells
are colored but can anyone help me with a formula or someting that will
tell me say for instance the colored cell's starts in cell B5 then it
would equal 5:00 but if it started in cell B6 then it would equal 6:00
also trying to give me the finishing time so based on what i said
earlier then the colored cell ends in B8 then it would equal 8:00?
Thanks!


--
darkbearpooh1
------------------------------------------------------------------------
darkbearpooh1's Profile:
http://www.excelforum.com/member.php...o&userid=30640
View this thread: http://www.excelforum.com/showthread...hreadid=513916