code to change font color of row

[Mark Kubicki]

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

 

[Nigel]

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