Change text Color on Updation of cell

rajivsoni

Board Regular
Joined
May 5, 2006
Messages
133
hey guys,
is there anyway i can change the color of the text if it is changed.. eg
a user enters the following date in A1 = 21/07/2007.
Now, if tomorrow he changes the date the date should be colored red.
(only if the date is different)
is tht possible???
pls help.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
Code:
Option Explicit

Dim mPrev

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"     '<== change to suit

    On Error GoTo ws_exit
    Application.EnableEvents = False

    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
        With Target
            If .Value <> mPrev Then
                If mPrev <> "" Then
                    .Interior.ColorIndex = 3
                End If
            End If
            mPrev = .Value
        End With
    End If

ws_exit:
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    mPrev = Target.Value
End Sub

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.



Updation? This is some peculiarly Indian word?
 

rajivsoni

Board Regular
Joined
May 5, 2006
Messages
133
Thanks a billion........ solves almost 90% of my problem.
just one more question...
if i want the color to change everytime i update a new value then ???
and i just want the text color to be changed NOT the entire Cell.
 

rajivsoni

Board Regular
Joined
May 5, 2006
Messages
133
alright... i got the font thinggy...
the only thing is tht everytime i change the value it shud give me a different color.
possible?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
.Font.ColorIndex = WorksheetFunction.RandBetween(1, 56)
 

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
Code:
Option Explicit

Dim mPrev

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"     '<== change to suit
Static nCI As Long

    On Error GoTo ws_exit
    Application.EnableEvents = False

    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
        With Target
            If .Value <> mPrev Then
                If mPrev <> "" Then
                    nCI = nCI + 1
                    If nCI > 56 Then nCI = 1
                    .Font.ColorIndex = 3
                End If
            End If
            mPrev = .Value
        End With
    End If

ws_exit:
    Application.EnableEvents = True
End Sub
 

Forum statistics

Threads
1,181,054
Messages
5,927,855
Members
436,573
Latest member
CMR237

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