Nigel,

Looks like you need to add "End If" immediately after "Next oCell".

Regards,
Jim Cone
San Francisco, USA



"Nigel Bennett" wrote in message
...
WHen ever I try and compile this code I get an error there
is a next without for, as far as I can see it should
compile fine

On Error GoTo errmsg
Dim value1, value2, value3 As String

value1 = Sheets("Menu").Range("G22")
value2 = Sheets("Menu").Range("G23")
value3 = Sheets("Menu").Range("G24")

Application.ScreenUpdating = True
Dim cell As Range, rng As Range
Dim sh As Worksheet
With Worksheets("Menu")
Set rng = .Range(.Cells(2, 14), .Cells(2, 14).End(xlDown))
End With
For Each cell In rng
Set sh = Worksheets(cell.Value)
Sheets(sh.Name).Activate

If Not Sheets("Menu").Range("G22") Is Nothing Then
Sheets(sh.Name).Range("B2").End(xlToRight).Offset( 0, 1) =
value1
Sheets(sh.Name).Range("B3").End(xlToRight).Offset( 0, 1) =
value2
Sheets(sh.Name).Range("B4").End(xlToRight).Offset( 0, 1) =
value3
End If

Dim oCell As Range
Dim LastCol As Range
Set LastCol = Cells(2, Columns.Count).End(xlToLeft)
Set rng = Range(Cells(2, 2), LastCol)
If Not Intersect(Target, Range("B2:Z2")) Is Nothing
Then
For Each oCell In rng
If oCell < "" Then
oCell.ColumnWidth = 12

Else
oCell.ColumnWidth = 2
End If

Next oCell

Application.Run Macro:=("EssMenuRetrieve")

Next