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
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