piece of code keeps failing....

  • Thread starter Thread starter Sam
  • Start date Start date
S

Sam

keep getting the error....

"compile error: Else without If"


Sub STEP1()

Dim FromWbook As Worksheet
Dim myCell As Range
Dim myRng1 As Range

Set FromWbook = Workbooks("Copy of 222.xls").Worksheets("Sheet2")

With FromWbook
Set myRng1 = .Range("E11:E71")
End With

For Each myCell In myRng1.Cells

If myCell.Value >= myCell.Offset(3, 0) Then
myCell.Offset(0, 1).Value = 1
myCell.Offset(0, 1).Select
myCell.Offset(0, 1).Font.ColorIndex = 4

With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

ElseIf myCell.Value < myCell.Offset(3, 0) Then
myCell.Offset(0, 1).Value = 1
myCell.Offset(0, 1).Select
myCell.Offset(0, 1).Font.ColorIndex = 4

With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Else
End If

Next myCell

End Sub
 
keep getting the error....

"compile error: Else without If"


Sub STEP1()

Dim FromWbook As Worksheet
Dim myCell As Range
Dim myRng1 As Range

Set FromWbook = Workbooks("Copy of 222.xls").Worksheets("Sheet2")

With FromWbook
Set myRng1 = .Range("E11:E71")
End With

For Each myCell In myRng1.Cells

If myCell.Value >= myCell.Offset(3, 0) Then
myCell.Offset(0, 1).Value = 1
myCell.Offset(0, 1).Select
myCell.Offset(0, 1).Font.ColorIndex = 4

With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

ElseIf myCell.Value < myCell.Offset(3, 0) Then
myCell.Offset(0, 1).Value = 1
myCell.Offset(0, 1).Select
myCell.Offset(0, 1).Font.ColorIndex = 4

With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Else
End If

Next myCell

End Sub
Click to expand...

In two places you have "With Selection.Interior" statements
without corresponding "End With" statements.

Correct this and see what happens.

Hope this helps / Lars-Åke
 
Sub STEP1()

Dim FromWbook As Worksheet
Dim myCell As Range
Dim myRng1 As Range

Set FromWbook = Workbooks("Copy of 222.xls").Worksheets("Sheet2")

With FromWbook
Set myRng1 = .Range("E11:E71")
End With

For Each myCell In myRng1.Cells

If myCell.Value >= myCell.Offset(3, 0) Then
myCell.Offset(0, 1).Value = 1
myCell.Offset(0, 1).Select
myCell.Offset(0, 1).Font.ColorIndex = 4

With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
END WITH
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

ElseIf myCell.Value < myCell.Offset(3, 0) Then
myCell.Offset(0, 1).Value = 1
myCell.Offset(0, 1).Select
myCell.Offset(0, 1).Font.ColorIndex = 4

With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
END WITH
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Else
End If

Next myCell

End Sub

ssions.microsoft.com> wrote in message
news:[email protected]...
 
Back
Top