Text colour blue when change is made

Corleone

Well-known Member
Joined
Feb 2, 2003
Messages
748
Office Version
  1. 365
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

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
748
Office Version
  1. 365
many thanks for all of your replies- this is just what i am lookin for
 

Corleone

Well-known Member
Joined
Feb 2, 2003
Messages
748
Office Version
  1. 365
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,990
Messages
5,834,743
Members
430,315
Latest member
bobh63

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