compile error Next without For


C

Carolyn

This is probably basic, but why do I get the Next without For compile
error if I change out the following lines?
vColor = 0
'with
If cell.Interior.ColorIndex = 6 Then
vColor = cell.Interior.ColorIndex
Else: vColor = 0

Here is the original code from David McRichie

Private Sub Worksheet_Change(ByVal Target As Range)
'David McRitchie, 2000-08-08 rev. 2000-08-14
' http://www.mvps.org/dmcritchie/excel/event.htm
Dim vLetter As String
Dim vColor As Integer
Dim cRange As Range
Dim cell As Range
'***************** check range ****
Set cRange = Intersect(Range("M5:AQ94"), Range(Target(1).Address))
If cRange Is Nothing Then Exit Sub
'**********************************
For Each cell In Target
vLetter = UCase(Left(cell.Value & " ", 1))

'If cell.Interior.ColorIndex = 6 Then
'vColor = cell.Interior.ColorIndex
'Else: vColor = 0

vColor = 0 'default is no color


Select Case vLetter
Case "S"
vColor = 34
Case "B"
vColor = 40
Case "T"
vColor = 39
Case "L"
vColor = 36
Case "X"
vColor = 38
Case "J"
vColor = 35
Case "V"
vColor = 37
Case "W"
vColor = 6

End Select
Application.EnableEvents = False 'should be part of Change
macro
cell.Interior.ColorIndex = vColor
Application.EnableEvents = True 'should be part of Change macro
Next cell
'Target.Offset(0, 1).Interior.colorindex = vColor
' use Text instead of Interior if you prefer
End Sub
 
Ad

Advertisements

Ad

Advertisements

T

Tushar Mehta

You are adding an If statement but don't have a closing End If. Try
If cell.Interior.ColorIndex = 6 Then
vColor = cell.Interior.ColorIndex
Else
vColor = 0
end if

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2004
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top