Work out Time in and out based on Cell format

[merilvingian]

Hey guys, I have a sheet that I want to calculate what time a shift starts based on a cell shading.

Basically Cells C6 and D6 need to be auto calculated based on the cells that are shaded from G6:L6

Look at the Attachment and you will see what I mean.

Thanks in advance.

[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

[merilvingian]

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

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

[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

[merilvingian]

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

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

[merilvingian]

Anyone had any luck yet?

[Leith Ross]

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

[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.

Please Login or Register  to view this content.

Worksheet Selection Change Event Code

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[merilvingian]

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