Thread: Work out Time in and out based on Cell format

Hello merilvingian,

I see you have a macro (UDF) written aand are using in with worksheet IF statements. What do you want to do differently?

Sincerely,
Leith Ross

Hi Thanks for the reply,

I would like the time to be worked out automatically. Not the hours worked, but when the line starts and finishes in each row. Each column represents half an hour.

So when the line starts on G6 and finishes on L6 That would be 6am to 9am

Thanks
Steven

Originally Posted by Leith Ross

Hello merilvingian,

I see you have a macro (UDF) written aand are using in with worksheet IF statements. What do you want to do differently?

Sincerely,
Leith Ross

Hello Steven,

I just noticed your in Brisbane. Is that Brisbane, CA? Just to be sure I understand this, you want the time in columns "C" and "D" to be filled in when the cells in columns "G:L" are colored?

Sincerely,
Leith Ross

Hey, Nah Brisbane Australia, and yes thats exactly what I want it to do.

Originally Posted by Leith Ross

Hello Steven,

I just noticed your in Brisbane. Is that Brisbane, CA? Just to be sure I understand this, you want the time in columns "C" and "D" to be filled in when the cells in columns "G:L" are colored?

Sincerely,
Leith Ross

Anyone had any luck yet?

Hello Steven,

It times like these that make me think the universe (perhaps multiverse) just likes to mess with me. My wife comes home from work sick, my computer crashes twice and now the forum is running really slow. So, I am going to rewrite the code, keep my fingers crossed and not beg the question.

Sincerely,
Leith Ross

Hello Steven,

Finally, it is done. Here is the macro that has been installed in the attached workbook. It updates the times once you move outside the "bars" table.

Sub GetStartAndFinish(Target As Range)

  Dim C As Long
  Dim Cell As Range
  Dim FinishTime As Variant
  Dim R As Long
  Dim Rng As Range
  Dim StartTime As Variant
  
    Set Cell = Target.Cells(1, 1)
    Set Rng = Range("G6:M14")
    
      If Not Intersect(Cell, Rng) Is Nothing Then
        R = Cell.Row
        Set Rng = Range(Cells(R, "G"), Cells(R, "M"))
          For Each Cell In Rng
            If Cell.Interior.ColorIndex = 1 Then
               If StartTime = 0 Then StartTime = Cell.Column
               FinishTime = Cell.Column
            End If
          Next Cell
        
         If StartTime <> 0 And FinishTime <> 0 Then
            Cells(R, "C") = Cells(3, StartTime)
            Cells(R, "D") = Cells(3, FinishTime)
         Else
            Cells(R, "C") = ""
            Cells(R, "D") = ""
         End If
      End If
      
End Sub

Worksheet Selection Change Event Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.EnableEvents = False
    GetStartAndFinish Target
  Application.EnableEvents = True
End Sub

Sincerely,
Leith Ross

WOW Thanks heaps. Awesome work.

And sorry to hear that your wife is sick, I hope she gets better.

Thank you so much.

Best regards
Steven