Conditional formatting / VBA, based on selected time range

Good Morning all,

Time again to ask the gurus another question.

Attached is a workbook. I have manually highlighted the cells to give you an idea of what I would like the outcome to be.

This is a bit like telling you guru's to suck eggs but I thought I would detail it as much as I could.

Anyway.....

Moonah OPS sheet.
In Row 5(Shift times), under the drop down menu in cells B5:J5, there is a range of Shift times.
And for the purpose of this I have selected
B5 as 0800:1621, manually highlighted B11:B27
C5 as 06:00, manually highlighted C7:C24
D5 as 07:00:1521, manually highlighted B11:B27
E5:I55(RDO, REC, SICK, Travel) can remain as manual input if it is to much to work out.

Is it possible or just a pipe dream, to use some method to automatically highlight a range of cells based on the C5:J5 'time'?

If the time is an actual shift time, the colour will be green and dark green writing as default, then we can allocate jobs/training/meetings and manually colour them.

What ya'll think?

Originally Posted by benishiryo

i don't know what it means. actual shift time referring to what you did in Column B:D? you can use dark green as the font in the Conditional Formatting window. but note that the Conditional Formatting supersedes the normal formatting. if it's dark green, you cannot manually overwrite it by formatting

Hi benishiryo,

Firstly, Thank you for taking the time to help.
In Row 5(Shift times), under the drop down menu in cells B5:J5, there is a range of Shift times, meaning anything time based option eg: 06:00-14:21, not REC, etc.

So it seems like conditional format wont work. What I would like to do is select say 06:00-14:21 in cell C5 for an employee and a CF or marco will automatically colour cells from C7:C24

Hi benishiryo,

Sorry if my last post offended you in any way. To further clarify the above and your concerns in the PM.

Please see attached. Daysheet DAY MONTH YEAR template - Highlight2.xlsx

I haven't added the TRAVEL CFormatting yet.

My meaning for the previous post. If I select, say, 08:00-16:21, the CF works great except I can not "re-colour" the cells (See example Column E), as you said CFormatting over rules anything else, hence the comment of

So it seems like conditional format wont work.

I probably should have added that extra detail in the original post, my apologies for that.


I hope this clarifies it slightly better for you.

Again, apologies and Thank you greatly.

Could a MOD please move this to the VBA forum with a link here?

Attached the updated workbook for the VBA gurus.

Daysheet DAY MONTH YEAR template - Highlight3.xlsx

Thank you FDibbins. Greatly appreciated.


benishiryo has been PM'd me for more information and asked that I put the following information.

Here is a normal kind of edit.

Untitled.jpg

Once I have selected the shift time for the officer(Row 5(B5:J5)), I manually colour it with a pre-configured Excel style(green)
Then go about manually allocating any jobs/duties for each officer, So you can see on the 06:00 start(Column I), the officer is doing "Express Post", I manually highlight the cells I7:I10, as the task usually takes 2 hours. So If an officer remains green, they may be available to do a urgent booking.

So yes there will be blank cells and cells with text

Hope the added information will help.

Bump

Any idea if a marco can do this?