Highlight Cells by duration

[leekb248]

Hi

I am using this layout so I can see what time a students lesson is and how long it is, What i would like is when I insert the duration that the Cells fill to that duration so its easier to see and scedule other lessons

 

[MARK858]

Is the below an option (it assumes that you are entering the name and start duration manually)?

Book1
	A	B	C
1	Time	9th July 2020	Duration
2	07:00
3	07:30
4	08:00
5	08:30	Abby Lesson	02:00:00
6	09:00	Abby Lesson	01:30:00
7	09:30	Abby Lesson	01:00:00
8	10:00	Abby Lesson	00:30:00
9	10:30
10	11:00
11	11:30	Mark Lesson	03:00:00
12	12:00	Mark Lesson	02:30:00
13	12:30	Mark Lesson	02:00:00
14	13:00	Mark Lesson	01:30:00
15	13:30	Mark Lesson	01:00:00
16	14:00	Mark Lesson	00:30:00
17	14:30
18	15:00
19	15:30
20	16:00	Jane Lesson	01:00:00
21	16:30	Jane Lesson	00:30:00
22	17:00	Fred Lesson	02:00:00
23	17:30	Fred Lesson	01:30:00
24	18:00	Fred Lesson	01:00:00
25	18:30	Fred Lesson	00:30:00
Sheet5

Cell Formulas
Range	Formula
B2:B4,B23:B25,B21,B12:B19,B6:B10	B2	=IF(A1="Time","",IF(C2<>"",B1,""))
C2:C4,C23:C25,C21,C12:C19,C6:C10	C2	=IF(C1="Duration","",IF(C1="","",IF(ROUND(C1,2)>TIMEVALUE("00:30"),C1-TIMEVALUE("00:30"),"")))

 

[leekb248]

Hi, sorry I forgot to mention that I would like it to fill with a colour rather than duplicate text

 

[MARK858]

That will probably need VBA if the time descending in column C isn't an option (might be able to do it with conditional formatting without the time descending in column C but I will have to think about that).
I might come back to it later if nobody else posts a solution.

I would like it to fill with a colour rather than duplicate text

P.S. the above is not how I interpreted the below part of the question from post #1

when I insert the duration that the Cells fill to that duration

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C
1	Time	9th July 2020	Duration
2	07:00:00
3	07:30:00
4	08:00:00
5	08:30:00	Abby Lesson	02:00:00
6	09:00:00
7	09:30:00
8	10:00:00
9	10:30:00
10	11:00:00
11	11:30:00	Mark Lesson	03:00:00
12	12:00:00
13	12:30:00
14	13:00:00
15	13:30:00
16	14:00:00
17	14:30:00
18	15:00:00
19	15:30:00
20	16:00:00	Jane Lesson	01:00:00
21	16:30:00
22	17:00:00	Fred Lesson	02:00:00
23	17:30:00
24	18:00:00
25	18:30:00
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:C25	Expression	=OR($C2<>"",AND($C2="",LOOKUP(2,1/($C$2:$C2<>""),$A$2:$A2)+LOOKUP(2,1/($C$2:$C2<>""),$C$2:$C2)>$A2))	text	NO