Search method - convert to VBA

A few days ago I posted a question (no replies) that I have since realised needs to be addressed in two parts, the first for which I need some help please.

In my Target sheet I have thousands of rows, under column “B” each cell holds a time value, column “A” needs to have a time period reference (ie: every 15min), this can be found in my TimePeriod sheet by searching the range A1:C35 to find the correct period and copy the reference to the cell alongside the time value back on my Target sheet.

The code below works and is shown as the basis of what I am trying to do, in its current form it is limited.

'=IF(AND(B8>='TimePeriod-table'!A1,B8<'TimePeriod-table'!B1),'TimePeriod-table'!C1,"")

I need to create a VBA macro, I believe it should loop through every row in my Target sheet, checking as to which period the TimeValue falls within my TimePeriod sheet by looping through the TimePeriod range. However, I am open to any suggestions that would be faster.

Can anyone please help me by converting the above code to run as VBA code.

Can it be done or not? Please help.