Help With Small Code . .

[Evagrius Ponticus]

Hi,

This code works once only - where did I go wrong?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Range("X16:AQ5000")
If (Range("E7") = "" Or Range("E8") = "") Then
.Font.ColorIndex = 40
Else
.Font.ColorIndex = 1
End If
End With
End Sub

 

[Scott Huish]

What do you mean it only works once? It seemed to work fine for me, although you don't need VBA to do this, it can be done in Conditional Formatting.

 

[Evagrius Ponticus]

well, I've already maxed my conditional formating - for some reason I keep getting an error if I activate cell e7 or e8? "Unable to set the color/index property of the font class."

 

[Evagrius Ponticus]

This is what I have now,

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("e7:e8,x16:AQ5000")) Is Nothing Then Exit Sub

With Range("X16:AQ5000")
If (Range("E7") = "" Or Range("E8") = "") Then
 ActiveSheet.Unprotect "7ptcnc"
.Font.ColorIndex = 40
Else
.Font.ColorIndex = 1
 ActiveSheet.Protect "7ptcnc"
End If
End With
End Sub