Worksheet Change function

[christianbiker]

Is it possible, using the below Private Sub, to set the selection to a range and if anything within that range is modified have the text within that cell or range changed to red?

Private Sub worksheet_change(ByVal target As Range)

Any help would be appreciated,
Thanks

 

[Macropheliac]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("Your Range Here") Then
        Target.Font.ColorIndex = 3
End If
End Sub

 

[christianbiker]

I have tried the code in a blank worksheet however it is giving me a mismatch 13 error. I am not sure why. I am putting it in the specific worksheet that I want changed.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A1:a100") Then
Target.Font.ColorIndex = 10
End If
End Sub

 

[Macropheliac]

Target refers to a single cell. Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
        Target.Font.ColorIndex = 3
End If
End Sub

 

[christianbiker]

Well that sort of works. It changes every time I click on the cell, even if I don't make any changes which is not what I want. I would like the colour to change only if a value changes within the range specified.

 

[Macropheliac]

My apologies

Private Sub Worksheet_Change(ByVal Target As Range) 

If Not Intersect(Target, Range("A1:A100")) Is Nothing Then 
        Target.Font.ColorIndex = 3 
End If 
End Sub

It needs Change instead of Selection Change Event.