How can I write this code in VBA

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!!!!

but needs to be faster, any suggestions?

Dim CheckSh As Variant
Dim TargetSh As Variant
Dim SearchVal As Variant
Dim rw1 As Long
Dim rw2 As Long
Dim myMsg As Variant

CheckSh = "TimePeriods"
TargetSh = "Trades"

For rw1 = 3 To Worksheets(TargetSh).Cells(65536, 3).End(xlUp).Row

SearchVal = Worksheets(TargetSh).Range("C" & rw1).Value


For rw2 = 1 To Worksheets(CheckSh).Cells(65536, 1).End(xlUp).Row

If SearchVal >= Worksheets(CheckSh).Range("A" & rw2) And SearchVal < Worksheets(CheckSh).Range("B" & rw2) Then Worksheets(CheckSh).Range("C" & rw2).Copy

Worksheets(TargetSh).Range("A" & rw1).Select
Worksheets(TargetSh).Paste

End If

Next rw2


Next rw1