I've got this code written to change the font color of a row based on the
value entered into column A of that row, but the color does not change

this code fires from within the worksheet -like an extended conditional
format
interesting note... I have a similar code written to update the color of all
the rows on the worksheet when the workbook itself opens, and that code
works fine

any suggestions would be greatly appreciated,
thanks in advance,
mark


Private Sub Worksheet_Change(ByVal target As Range)
Call UpdateRowColor(target)
End Sub

Sub UpdateRowColor(target As Range)
Dim response As Boolean
Dim vPhase As String
Application.ScreenUpdating = False
If Not Intersect(target, Range("A:A")) Is Nothing Then
If target.Count > 1 Then
response = MsgBox("Please select only 1 row at a time; (color
will not update otherwise)", vbOKOnly, "MULTILE ROWS SELECTED")
Exit Sub
Else
vPhase = UCase(Trim(Range("A" & target.Row).Value))
Select Case vPhase
Case Is = "CA"
Rows(target.Row & ":" &
target.Row).Font.ColorIndex = 45
Case Is = ...
End Select
End If
End If
Application.ScreenUpdating = True
End sub

Your code is causing a infinite loop! When you change the entire row you
are re-triggering the event!

Disable events before updating and re-enable after. You are also exiting the
loop (Exit Sub) before restoring screen updating, try changing the code as
follows....

Sub UpdateRowColor(target As Range)
Dim response As Boolean
Dim vPhase As String
Application.ScreenUpdating = False
Application.EnableEvents = False
If Not Intersect(target, Range("A:A")) Is Nothing Then
If target.Count = 1 Then
vPhase = UCase(Trim(Range("A" & target.Row).Value))
Select Case vPhase
Case Is = "CA"
Rows(target.Row & ":" &
target.Row).Font.ColorIndex = 45
'Case Is = ...
End Select
Else
MsgBox "Please select only 1 row at a time; (color will not update
otherwise)", vbOKOnly, "MULTIPLE ROWS SELECTED"
End If
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

--

Regards,
Nigel
(E-Mail Removed)



"Mark Kubicki" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> I've got this code written to change the font color of a row based on the
> value entered into column A of that row, but the color does not change
>
> this code fires from within the worksheet -like an extended conditional
> format
> interesting note... I have a similar code written to update the color of
> all the rows on the worksheet when the workbook itself opens, and that
> code works fine
>
> any suggestions would be greatly appreciated,
> thanks in advance,
> mark
>
>
> Private Sub Worksheet_Change(ByVal target As Range)
> Call UpdateRowColor(target)
> End Sub
>
> Sub UpdateRowColor(target As Range)
> Dim response As Boolean
> Dim vPhase As String
> Application.ScreenUpdating = False
> If Not Intersect(target, Range("A:A")) Is Nothing Then
> If target.Count > 1 Then
> response = MsgBox("Please select only 1 row at a time; (color
> will not update otherwise)", vbOKOnly, "MULTILE ROWS SELECTED")
> Exit Sub
> Else
> vPhase = UCase(Trim(Range("A" & target.Row).Value))
> Select Case vPhase
> Case Is = "CA"
> Rows(target.Row & ":" &
> target.Row).Font.ColorIndex = 45
> Case Is = ...
> End Select
> End If
> End If
> Application.ScreenUpdating = True
> End sub
>