Text colour blue when change is made

[Corleone]

I would like to insert some code into a worksheet so that whenever a user makes a change to any of the text on that sheet, it turns blue

any help appreciated


thanks

 

[DauntingGecko]

Put this into the sheet code (not a normal module). Remember to change the range you want to apply it to.

Private Sub Worksheet_Change(ByVal Target As Range)
    Range("A1:Q1000").Font.Color = vbBlue
End Sub

 

[Mike Blackman]

Hi Gecko,

I assume the Op only wants the changed text to turn blue.

Private Sub Worksheet_Change(ByVal Target As Range)

    Target.Font.Color = vbBlue

End Sub

 

[MickG]

There are obvioulsy ways round this, but this code will turn Blue the Text in any cell that is altered.
New Text in a cell will be Black until altered.
Right click sheet tab, Select "Viewcode" (VB window appears) Paste at TOP of VB window.

Dim rng As Range, txt As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
If Not rng Is Nothing Then
    If Not Target.Address = rng.Address Then
        If Not rng.value = txt And Not txt = vbNullString Then
            rng.Font.ColorIndex = 5
        End If
    End If
End If
Set rng = Target
txt = Target
End If
End Sub

Mick

 

[Mike Blackman]

Hi Mick,

You have a SelectionChange event there, is that what you meant?

 

[MickG]

Hi, Mike
Yes it rather over kill but, It is subtly different from your code, It compares the Cell text before and after its altered.
If you enter data in a cell it does not change color, if you overwrite data with the same data it does not change color.
It only changes when you actualy alter some text that already exists.
There's Probably a better and simpler way to achieve all this, I just haven't thought of it !!!
It would be nice to change the colours of the individual characters that change, but that need a bit more thinking.
Regards Mick

 

[Corleone]

many thanks for all of your replies- this is just what i am lookin for

 

[Corleone]

There are obvioulsy ways round this, but this code will turn Blue the Text in any cell that is altered.
New Text in a cell will be Black until altered.
Right click sheet tab, Select "Viewcode" (VB window appears) Paste at TOP of VB window.

Dim rng As Range, txt As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
If Not rng Is Nothing Then
    If Not Target.Address = rng.Address Then
        If Not rng.value = txt And Not txt = vbNullString Then
            rng.Font.ColorIndex = 5
        End If
    End If
End If
Set rng = Target
txt = Target
End If
End Sub

Mick

got one other question regarding this

when i change a cell item it works fine, however, the option to undo is now not available
is there a way of retaining the undo function when using this solution?

thanks