Text Color Change
 

Hi,

The change event macro below is not working. Is there a mistake in the
formula that I've missed?

Thanks!


__________________________________________________ __

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim vLetter As String
Dim vColor As Integer
Dim cRange As Range
Dim cell As Range

Set cRange = Intersect(Range("B3:X28"), Range(Target(1).Address))
If cRange Is Nothing Then Exit Sub

For Each cell In Target
vLetter = UCase(Left(cell.Value & " ", 1))
vColor = 0
Select Case vLetter
Case "V"
vColor = 3
Case "S"
vColor = 3
Case "E"
vColor = 3
Case "P"
vColor = 3
Case "T"
vColor = 3

End Select
Application.EnableEvents = False
cell.Text.ColorIndex = vColor
Application.EnableEvents = True
Application.Calculate
Next cell
End Sub

--
Message posted via http://www.officekb.com

Hi Kim

it seems to work when you change
cell.Text.ColorIndex = vColor
to
cell.Font.ColorIndex = vColor

Cheers
julieD

"Kim via OfficeKB.com" wrote in message
...
Hi,

The change event macro below is not working. Is there a mistake in the
formula that I've missed?

Thanks!


__________________________________________________ __

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim vLetter As String
Dim vColor As Integer
Dim cRange As Range
Dim cell As Range

Set cRange = Intersect(Range("B3:X28"), Range(Target(1).Address))
If cRange Is Nothing Then Exit Sub

For Each cell In Target
vLetter = UCase(Left(cell.Value & " ", 1))
vColor = 0
Select Case vLetter
Case "V"
vColor = 3
Case "S"
vColor = 3
Case "E"
vColor = 3
Case "P"
vColor = 3
Case "T"
vColor = 3

End Select
Application.EnableEvents = False
cell.Text.ColorIndex = vColor
Application.EnableEvents = True
Application.Calculate
Next cell
End Sub

--
Message posted via http://www.officekb.com

thanks!

--
Message posted via http://www.officekb.com

you're welcome

"Kim via OfficeKB.com" wrote in message
...
thanks!

--
Message posted via http://www.officekb.com