error when changing font color with VBA based on cell value

[RatherBeRidi]

The code below changes the font color to red if edits are made to the data. Works fine, except SOMETIMES if the font color is already red, I get a run-time error 1004: Application-defined or object-defined error. I'm at a loss for why it only happens sometimes, and how to correct the problem. Thanks for helping out a novice.



Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
If Not Intersect(Target, Range("$A:$P")) Is Nothing Then
For Each Cell In Target
If Cell.Value <> "" Then
Cell.Font.ColorIndex = 3 ' Set to red
End If
Next Cell
End If
End Sub

[CatDaddy]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
If Not Intersect(Target, Range("$A:$P")) Is Nothing Then
For Each Cell In Target
If Cell.Value <> "" Then
On Error Resume Next
Cell.Font.ColorIndex = 3 ' Set to red
On Error Goto 0
End If
Next Cell
End If
End Sub

[Kenneth Hobs]

Works fine for me. I might suggest that you turn off events.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
On Error GoTo EndSub
Application.EnableEvents = False
If Not Intersect(Target, Range("$A:$P")) Is Nothing Then
For Each Cell In Target
With Cell
If .Value <> "" Then
.Font.ColorIndex = 3 ' Set to red
End If
End With
Next Cell
End If
EndSub:
Application.EnableEvents = True
End Sub

[Opv]

Is the For loop really needed in this instance?

[Kenneth Hobs]

If you want to be literal, yes, but in a practical sense, no.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
If Not Intersect(Target, Range("$A:$P")) Is Nothing Then
Target.Font.ColorIndex = 3 ' Set to red
End If
End Sub

[Opv]

If you want to be literal, yes, but in a practical sense, no.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
If Not Intersect(Target, Range("$A:$P")) Is Nothing Then
Target.Font.ColorIndex = 3 ' Set to red
End If
End Sub

Thanks.