Change text Color on Updation of cell

[rajivsoni]

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.

 

[rajivsoni]

help me pleeeeeeeeeeeeeeeeeeeeeeeeeeeeez

 

[xld]

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]

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]

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]

Try

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

 

[xld]

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