color help

[darkbearpooh1]

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!

 

[Bob Phillips]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[darkbearpooh1]

That works perfect! Thanks,

But I need to modify it to work for me, How can I get it to count
across. as an example range A1-E1 instead of down ? and Also I need to
count each cell as 30 minutes if possible? Thank you!!!!

 

[Bob Phillips]

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.Column To rng.Columns.Count
If rng.Columns(i).Interior.ColorIndex = ci Then
GetTime = TimeSerial(Int(i / 2), (i Mod 2) * 30, 0)
Exit For
End If
Next i
Else
For i = rng.Columns.Count To rng.Column Step -1
If rng.Columns(i).Interior.ColorIndex = ci Then
GetTime = TimeSerial(Int(i / 2), (i Mod 2) * 30, 0)
Exit For
End If
Next i
End If
End Function



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"darkbearpooh1" <[email protected]>
wrote in message
news:[email protected]...

 

[darkbearpooh1]

Thats great! but the problem i am running into now is my coloring of
cells for the time starts at 6:00 or 6am but the furthest I can start
my coloring is in column F so it = 3:00 or 3am using the calling
=GetTime(A28:AQ28,36,TRUE) A=12 by the time you get to F it is only 3am
so is there a way to start column F at 6:00 or 6am?

 

[darkbearpooh1]

I figured it out i just had to add more columns in the start at column A
and shirnk the width down to zero so it wasn't visable This is so
awsome! Thank you so much for the help Bob couldn't have gotten this
far without your help! Everyone on this site is awsome!!! now i am
going to post another question because i need to figure out how to
change the time format from going to military time as in 13:00 and just
loop as 1:00 again