Text colour blue when change is made

Corleone

Well-known Member
Joined
Feb 2, 2003
Messages
714
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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

DauntingGecko

Well-known Member
Joined
Feb 2, 2006
Messages
521
Put this into the sheet code (not a normal module). Remember to change the range you want to apply it to.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Range("A1:Q1000").Font.Color = vbBlue
End Sub
 

Mike Blackman

Well-known Member
Joined
Jun 8, 2007
Messages
2,494
Hi Gecko,

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

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Target.Font.Color = vbBlue

End Sub
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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.
Code:
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

Well-known Member
Joined
Jun 8, 2007
Messages
2,494

ADVERTISEMENT

Hi Mick,

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

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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

Well-known Member
Joined
Feb 2, 2003
Messages
714
many thanks for all of your replies- this is just what i am lookin for
 

Corleone

Well-known Member
Joined
Feb 2, 2003
Messages
714
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.
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top