Cells(3,1) is A3

You said you had numbers in column A, not formulas. Nonetheless:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target _
As Range, Cancel As Boolean)
Dim rng as Range
On Error GoTo ErrHandler
If Target.count 1 then exit sub
If Target.column = 3 Then
Application.EnableEvents = False
set rng = Cells(target.row,1).Resize(1,2)
Target.Formula = "=Sum(" & rng.Address(0,0) & ")"
Target.Interior.ColorIndex = xlNone
Cancel = True
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.count 1 then exit sub
If Target.Column = 3 Then
If Target.HasFormula Then
Target.Interior.ColorIndex = xlNone
Else
Target.Interior.ColorIndex = 3
End If
End If
End Sub


--
Regards,
Tom Ogilvy


"jaccker" wrote in
message ...

thank you very much. it is very useful. In fact, the problem I have is
more generel. Let's say, for the first colum(A), I have 10 numbers. For
the second colum(B), I have another 10 numbers. The third colum is the
formula of sum of the corresponding cells in first two colum. For the
thrid colum, I need the following functionality, for example, for
Cells(3,1),

Once I change the formula in Cells(3,1), the color of the cell
Cells(3,1) should be changed.
Once I double click the changed cell Cells(3,1), it should recover to
the formula "=SUM(A1+B1)".

Thank you again for your help


--
jaccker
------------------------------------------------------------------------
jaccker's Profile:
http://www.excelforum.com/member.php...o&userid=33116
View this thread: http://www.excelforum.com/showthread...hreadid=529226