Is there a way I can have cells (starting cell, inbetween cells and finishing cell) automatically change color based on a time input

[Paul365]

Hello,

I was wondering if it is possible I could have cells which have time attached in one row (every 15 minutes) ex. A8=7:00am A9= 7:15am, A10 = 7:30am etc. until 11:45pm automatically change color when a start time is inserted and finishing time in a different cell.

Example:
B1:F1 are labeled venues
Cells B2:F7 is the cells which i want the start time and finish time to be entered
Cells A8:A75 is the time designated as reference

If I enter a start time in B2 (lets say 8am) and Finish Time in B3 (lets say 9am) I would like cell B12:15 automatically highlight a certain color.

Is this a possible formula or VBA which can be made?

Please see screen shots to help with example

Any help would be appreciated

Thanks

Paul

 

[JamesCanale]

I got this as one rule and one color - but you can break up the conditional formatting rules to use different colors

MrExcelPlayground13.xlsx
	J	K	L
1	Venue	Room1	Room2
2	Start1	8:00	7:00
3	Finish1	10:00	8:00
4	Start2		9:00
5	Finish2		10:30
6	Start3
7	Finish3
8	7:00
9	7:15
10	7:30
11	7:45
12	8:00
13	8:15
14	8:30
15	8:45
16	9:00
17	9:15
18	9:30
19	9:45
20	10:00
21	10:15
22	10:30
Sheet12

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K8:L22	Expression	=OR(AND($J8>=K$2,$J8<K$3),AND($J8>=K$4,$J8<K$5),AND($J8>=K$6,$J8<K$7))	text	NO

 

[Paul365]

This is amazing, thanks so much.

I have tried it for the morning time and works great, however as soon as I put any times in after 12:00 pm, it always adds an extra colored cell at the end or if started before, it subtracts a colored cell- is there a way to stop this?

It should start and finish where I placed the yellow highlighter.

Prior to 12:00 pm it works amazing.

Thanks so much for your help and assistance

 

[JamesCanale]

Could it be how you are writing the time? It often defaults to AM if you just put in 12:00 or 2:00. The thing works fine for me when I use 13:30 to 14:30. for noon - I often put in ".5"

 

[Paul365]

Hey, sorry been a while - got caught up.

it works until you add more than one timing in, especially when it is after 3pm now. It seems to loose a cell.
See screen shot

 

[Paul365]

Amazing - It works, I had to re make the sheet to get it to work, but thanks so much.

I do have another question now - Is there a way I can make it count multiple times

Thanks

Example:

 

[smozgur]

Amazing - It works, I had to re make the sheet to get it to work, but thanks so much.

The marked solution post has been switched accordingly.


@Paul365: In your future questions, please mark the post as the solution that answered your question instead of your feedback post, so it will help future readers. There is no further action required in this thread.

Also - if you have "another question" even related to a question that was solved already, and if it is a separate question that is not an adjustment on the provided answer then please start another thread, and then you can refer to the related thread by providing its link in your new question.