How to write VBA code to solve this problem

[jaccker]

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

 

[Bob Phillips]

See response in .misc

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

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

 

[jaccker]

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

 

[Tom Ogilvy]

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