F
Fred Newton
Courtesy of this group I have set up the following Worksheet_Change
event, however i am experiencing a problem where by the fill colour
changes but the font does not, unless I use the tick button to accepr
the change to the cell. I I use the enter key, the font colour of the
cell below is changed, but not the fill colour.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim vLetter As String
Dim vColor As Integer
Dim fcolour As Integer
Dim cRange As Range
Dim cell As Range
'***************** check range ****
Set cRange = Intersect(Range("A3:IV3"), Range(Target(1).Address))
If cRange Is Nothing Then Exit Sub
'**********************************
For Each cell In Target
vLetter = cell.Value & " "
vColor = 0 'default is no color
Select Case vLetter
Case "R ", "r "
vColor = 3
fcolour = 2
Case "A ", "a "
vColor = 27
fcolour = 1
Case "G ", "g "
vColor = 10
fcolour = 2
Case "C ", "c ", "D ", "d ", "P ", "p "
vColor = 41
fcolour = 2
Case "H ", "h "
vColor = 9
fcolour = 2
Case " "
vColor = 0
fcolour = 1
End Select
Application.EnableEvents = False 'should be part of Change macro
cell.Interior.ColorIndex = vColor
With Selection.Font
.ColorIndex = fcolour
End With
Application.EnableEvents = True 'should be part of Change macro
Next cell
End Sub
Can someone point out the error of my vb ways please ?
Thanks in advance
Fred Newton
Zurich Financial Services
event, however i am experiencing a problem where by the fill colour
changes but the font does not, unless I use the tick button to accepr
the change to the cell. I I use the enter key, the font colour of the
cell below is changed, but not the fill colour.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim vLetter As String
Dim vColor As Integer
Dim fcolour As Integer
Dim cRange As Range
Dim cell As Range
'***************** check range ****
Set cRange = Intersect(Range("A3:IV3"), Range(Target(1).Address))
If cRange Is Nothing Then Exit Sub
'**********************************
For Each cell In Target
vLetter = cell.Value & " "
vColor = 0 'default is no color
Select Case vLetter
Case "R ", "r "
vColor = 3
fcolour = 2
Case "A ", "a "
vColor = 27
fcolour = 1
Case "G ", "g "
vColor = 10
fcolour = 2
Case "C ", "c ", "D ", "d ", "P ", "p "
vColor = 41
fcolour = 2
Case "H ", "h "
vColor = 9
fcolour = 2
Case " "
vColor = 0
fcolour = 1
End Select
Application.EnableEvents = False 'should be part of Change macro
cell.Interior.ColorIndex = vColor
With Selection.Font
.ColorIndex = fcolour
End With
Application.EnableEvents = True 'should be part of Change macro
Next cell
End Sub
Can someone point out the error of my vb ways please ?
Thanks in advance
Fred Newton
Zurich Financial Services