keep getting the error message - Compile error: Else without If

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

Sam

keep getting the error message

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 message

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
 
Yeah, I think it's what Lars said, you were missing a couple 'End With'
statements. Try this:
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

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

End If
End If

Next myCell

End Sub

**NOTICE: try the code on a copy of your data, in case the code doesn't do
what you want it to do...it's much easier to revert to a backup copy than
redo the whole project...or whatever.

HTH,
Ryan---
 
Back
Top