Creating Dynamic Range using Variables in VBA creates Runtime 1004 Error

I been trying to help my son with a task, which selects staff who have a start and finish time. He wanted to highlight their shift with a coloured/greyed background on a separate sheet.
I've simply put a spreadsheet together with two sheets and entered some basic VBA code to automate this process.

The problem occurs when I want to select the range that is dynamically created based on the staff members start and finishing times. I get the usual runtime error '1004' - application-defined or object-defined error.
Anybody know how I can get this to work, as I'm sure it must be something simple? I've attached the spreadsheet (macro-enabled with no password) but the code's here anyway.
BTW: The function at the head of the code supplies the column letter from column number of the selected cell.

Sheet1 has the following:
Name Start Finish
Name 1 9:00 AM 5:00 PM
Name 2 6:00 AM 6:00 PM
Name 3 12:00 PM 8:00 PM
Name 4 7:30 AM 4:30 PM

Sheet2
Name 6:00 AM 6:30 AM 7:00 AM 7:30 AM 8:00 AM (...with the series extended to 8:00 PM)
Name 1
Name 2
Name 3
Name 4

Please Login or Register  to view this content.

Originally Posted by protonLeah

Please Login or Register  to view this content.

Thanks but I've tried this already. It comes up with the same '1004' error. I appreciate your feedback though.
BTW: I'm using Excel 2013

A different code. I do not know what you want to do on sheet2. The code puts the "Start" and "Finish" words on each row on sheet2. I could change if you want to colour them.

Originally Posted by MarvinP

Or try to change the last two lines to this:

Please Login or Register  to view this content.

Thanks for responding MarvinP.
As with protonLeah, I've tried this myself, in a number of different combinations but without success. Hence the reason for putting it up here. It's probably something fundamental that I'm blissfully unaware of. Again, thanks for your input. It's much appreciated that you both have taken time to have a look at the problem and proposed a fix.

Originally Posted by AB33

A different code. I do not know what you want to do on sheet2. The code puts the "Start" and "Finish" words on each row on sheet2. I could change if you want to colour them.

Thanks AB33. That works and yes, the aim was to just select the range of the staff member's shift and change the background colour.
I would still like to understand why you seemingly can't simply use variables to declare a varying range. It's definitely something to do with Objects vs standard variables, declarations or something of that ilk. I'm stumped, so I'd rather discover WHY my method doesn't work, because logically I think it should, or am I just being naive?

With due respect, the code you have is prone to error as you have used select method. Select method does not do much but works depending on the active sheet or range that is, where ever your cursor happens to be when you run the code.
The other error is if you use a find function, you need to set the range as objects, i.e. use set as already highlighted to you.
You also need to test if the code finds(match), if not it errors unless you put in a condition like

Please Login or Register  to view this content.

Your code does not have a color line? Even it works, you need to add a line which color the matched rows and columns.

I have added another code on module 2. It is the same code as you have, i.e. the find function, and you get the same result as that of my first code, so you 2 different solutions.

Eureka! I have just managed to get it to work.

All I had to do was replace:

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

Job done, well not quite! I had to add:

Please Login or Register  to view this content.

before

Please Login or Register  to view this content.

then, to tidy it up added

Please Login or Register  to view this content.

at the top of the Declarations.

Thanks to all who contributed and thankfully it's another mystery solved. Hope it helps somebody else in the future. Even me, as I'm at that age!