Trouble with VBA CODE HELP...

[bloodmilksky]

Hi Guys,

I am using the below code to return some information from a sheet under the name of "lenses" based on a value entered into cell B7 and it should return this information into B29 but everytime I try and run it nothing happens.

can anyone help me please?

Private Sub Worksheet_Change(ByVal Target As Range)
' Defines variables
Dim FindString As String, Rng As Range, sRange As Range, RowNo As Integer, Limit As Long

' Defines LastRowMenu as last row of column B of the Menu sheet containing data
LastRowMenu = Sheets("Request Form").Cells(Rows.Count, "B").End(xlUp).Row + 1

' Defines LastRow as last row of column B of the Lenses sheet containing data
LastRow = Sheets("Lenses").Cells(Rows.Count, "B").End(xlUp).Row

' If target cell is A1 then...

If Not Intersect(Target, Range("B7")) Is Nothing Then
' If target value is not blank then...

If Target.Value <> "" Then

' Sets FindString as the value of A1 (case insensitive)
FindString = UCase(Target.Value)

' Sets search range as the used range on the Lenses sheet
Set sRange = Sheets("Lenses").UsedRange

' For each cell in the search range
For Each Cell In sRange

' If the cell contains the FindString value (case insensitive) then...
If InStr(1, UCase(Cell.Value), FindString) Then

' Copy the 8x3 block to the next blank row of the lenses section of the Menu sheet
Sheets("Lenses").Range(Cell, Cell.Offset(33, 2)).Copy
Sheets("Request Form").Range("B" & LastRowMenu).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

' Draw in the gridlines
With Selection.Borders
.LineStyle = xlContinuous
.Weight = xlThin
End With

' Put a thick border aound the 8x3 block
Selection.BorderAround Weight:=xlMedium

' Increase LastRowMenu by 8 to account for the new data
LastRowMenu = LastRowMenu + 8
End If

' Check next cell in search range
Next Cell

' If the name was not found then...
If Range("B29") = "" Then

' Display an error stating the name is not in the list
MsgBox "Specified name does not exist", vbOKOnly, "Attention!"

' Clear the contents of A1
Target.ClearContents

' Reselect cell A1
Range("A10").Select
End If

' Else if A1 is empty...
Else

' Clear the contents of Lenses section on the Menu sheet
Range("B29:D" & LastRowMenu).ClearContents

End If
End If
End Sub