Color Cell When Cell Value is actually Changed

[imakshar]

I am pretty new to Excel Macros & I have following code in my current spreadsheet as macro to highlight a cell when cell value changes for Column C. The way it works is
****************************************************
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 Then Exit Sub 'Do not change Row 1
If Target.Column <> 3 Then Exit Sub 'only allow changes to Col C
Range(Target.Address).Activate
If ActiveCell <> "" Then
ActiveCell.Interior.ColorIndex = 27 'change to colour of your choice
Else
ActiveCell.Interior.ColorIndex = -4142
End If
End Sub
****************************************************
1. when any cell in Column C is selected (i mean double click) than it changes the color assuming that active cell value changed.
2. when any cell value is edited, it changes the color.
3. if i just select a cell (no double click) and hit delete button, cell is cleared but not recognized as cell change so no color in that cell.

What i am trying to achieve is when a cell value is edited (and only edited .. it includes delete, backspace, insert etc) than it changes the color. Just double click should not change the color as it has not edited yet.

Can someone please help me with right code ?

 

[Mohammad Basem]

Hi imakshar and welcome to the forum.

Try this, copy the below code to the Worksheet code window

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 And Target.Column <> 3 Then Exit Sub [COLOR="Green"]'only allow changes to Col C[/COLOR]
    SetCellColor Target
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    SaveOldValue Target
End Sub[/COLOR][/SIZE][/FONT]

Insert new Module and copy the below code to its code window

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Option Explicit

Private vCurrentValue As Variant

Sub SaveOldValue(Target As Range)
    vCurrentValue = Target.Value
End Sub

Sub SetCellColor(Target As Range)

    If Target = vCurrentValue Then Exit Sub
    If Target <> "" Then
        Target.Interior.ColorIndex = 27 [COLOR="Green"]'change to colour of your choice[/COLOR]
    Else
        Target.Interior.ColorIndex = -4142
    End If

End Sub[/COLOR][/SIZE][/FONT]

 

[Mohammad Basem]

Just discovered a typo mistake

please replace Worksheet_Change with this one

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 Or Target.Column <> 3 Then Exit Sub [COLOR="Green"]'only allow changes to Col C[/COLOR]
    SetCellColor Target
End Sub[/COLOR][/SIZE][/FONT]