Multiple condtion For loop, dynamic named range

Hi,

This is my first play with VBA so apologise if the question is very easy and but I've been running into errors I cant resolve for the last couple of days.
(if someone can suggest a good debugging tutorial would also be appreciated).

I have a spreadsheet of building data energy use (15min intervals for many days, I've attached just two days). I'm trying to extract the time ranges where use is high/low or ramping up and down.

All I think I need is a for loop, which loops through a named range and returns the value of the adjacent time column for the first cell which exceeds a bound (in this case this is the variable "mid"), following this I want to continue looping through the range and returns the time value where the value of the range drops below the variable "mid".

My first problem is in this implementation loop, it gives an application/object definition error, which so far I can't resolve.

I've attached the spreadsheet with my current VBA code

Thanks for your time