The code below works but is limited in its current form for what I need to do. Can anyone either show me the correct way to write this in VBA or point me in the right direction.

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

Basically its saying if the value in current worksheet "mycell-A" is greater than or equal to "mycell-A" and less than "Mycell-B" in another worksheet, then value of myVal = "mycell-C".

I can't believe that this can beat the best of you!!!!