How to write VBA code to solve this problem
 

I have a question about the using of Excel and VBA.

Let’s say in cell A1 and A2, I input 1 and 2. Then I input formula
“=SUM(A1+A2)” into cell A3.

I would like to write a VBA code to have the following functionality:

Once I change the formula in A3, let’s say I input 4 to cell A3, the
color of the cell A3 should be changed.
Once I double click the changed cell A3, it should recover to the
formula “=SUM(A1+A2)”.

Anybody could help me out to solve the problem?

Thank you


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

How to write VBA code to solve this problem
 

See response in .misc

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jaccker" wrote in
message ...

I have a question about the using of Excel and VBA.

Let's say in cell A1 and A2, I input 1 and 2. Then I input formula
"=SUM(A1+A2)" into cell A3.

I would like to write a VBA code to have the following functionality:

Once I change the formula in A3, let's say I input 4 to cell A3, the
color of the cell A3 should be changed.
Once I double click the changed cell A3, it should recover to the
formula "=SUM(A1+A2)".

Anybody could help me out to solve the problem?

Thank you


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

How to write VBA code to solve this problem
 

right click on the sheet tab and select view code. Then put in code like this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target _
As Range, Cancel As Boolean)
On Error GoTo ErrHandler
If Target.Address = "$A$3" Then
Application.EnableEvents = False
Target.Formula = "=Sum(A1:A2)"
Target.Interior.ColorIndex = xlNone
Cancel = True
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$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:


I have a question about the using of Excel and VBA.

Lets say in cell A1 and A2, I input 1 and 2. Then I input formula
€œ=SUM(A1+A2)€� into cell A3.

I would like to write a VBA code to have the following functionality:

Once I change the formula in A3, lets say I input 4 to cell A3, the
color of the cell A3 should be changed.
Once I double click the changed cell A3, it should recover to the
formula €œ=SUM(A1+A2)€�.

Anybody could help me out to solve the problem?

Thank you


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

How to write VBA code to solve this problem
 

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

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

Thank you again for your hel

--
jaccke
-----------------------------------------------------------------------
jaccker's Profile: http://www.excelforum.com/member.php...fo&userid=3311
View this thread: http://www.excelforum.com/showthread.php?threadid=52922

How to write VBA code to solve this problem
 

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