PDA

View Full Version : error when changing font color with VBA based on cell value



RatherBeRidi
06-19-2012, 07:26 AM
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
06-19-2012, 07:57 AM
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
06-19-2012, 07:58 AM
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
06-19-2012, 08:48 AM
Is the For loop really needed in this instance?

Kenneth Hobs
06-19-2012, 09:27 AM
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
06-19-2012, 10:02 AM
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.