adopting a macro to a general situation.
 

Hi,
I've created the below macro. By using the ChangeValue or a similar call, I
need to adopt this macro in order to be able to change adjacent D date value
to TODAY's date if I ever change any currency value on column C.
Can anyone suggest an alteration?.
=======================
Sub Macro1()
Range("C2").Select
ActiveCell.FormulaR1C1 = "27000"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("D3").Select
End Sub
=============================
TIA
Martyn




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22.05.2004

adopting a macro to a general situation.
 

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column = 3 Then
.Offset(0,1).Value = Date
End If
End With

ws_exit:
Application.EnableEvents = True
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.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Martyn" wrote in message
...
Hi,
I've created the below macro. By using the ChangeValue or a similar call,
I
need to adopt this macro in order to be able to change adjacent D date
value
to TODAY's date if I ever change any currency value on column C.
Can anyone suggest an alteration?.
=======================
Sub Macro1()
Range("C2").Select
ActiveCell.FormulaR1C1 = "27000"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("D3").Select
End Sub
=============================
TIA
Martyn




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22.05.2004

adopting a macro to a general situation.
 

In the worksheet's code module, use the following:

Option Explicit
Private Sub updateACell(aCell As Range)
Dim aCell As Range
If aCell.Column = 3 Then
On Error Resume Next
Application.EnableEvents = False
aCell.Offset(0, 1).Value = Date
Application.EnableEvents = True
On Error GoTo 0
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then
Dim aCell As Range
For Each aCell In Target
updateACell aCell
Next aCell
Else
updateACell Target
End If
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hi,
I've created the below macro. By using the ChangeValue or a similar call, I
need to adopt this macro in order to be able to change adjacent D date value
to TODAY's date if I ever change any currency value on column C.
Can anyone suggest an alteration?.
=======================
Sub Macro1()
Range("C2").Select
ActiveCell.FormulaR1C1 = "27000"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("D3").Select
End Sub
=============================
TIA
Martyn




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22.05.2004

adopting a macro to a general situation.
 

Bob you are great........:)
Thanks a lot

"Bob Phillips" wrote in message
...
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column = 3 Then
.Offset(0,1).Value = Date
End If
End With

ws_exit:
Application.EnableEvents = True
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.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Martyn" wrote in message
...
Hi,
I've created the below macro. By using the ChangeValue or a similar
call,
I
need to adopt this macro in order to be able to change adjacent D date
value
to TODAY's date if I ever change any currency value on column C.
Can anyone suggest an alteration?.
=======================
Sub Macro1()
Range("C2").Select
ActiveCell.FormulaR1C1 = "27000"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("D3").Select
End Sub
=============================
TIA
Martyn




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22.05.2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22.05.2004

adopting a macro to a general situation.
 

Thank you Tushar,
now I have two solutions...:)

"Tushar Mehta" wrote in message
news:MPG.1b1d4287e55456649897f9@news-server...
In the worksheet's code module, use the following:

Option Explicit
Private Sub updateACell(aCell As Range)
Dim aCell As Range
If aCell.Column = 3 Then
On Error Resume Next
Application.EnableEvents = False
aCell.Offset(0, 1).Value = Date
Application.EnableEvents = True
On Error GoTo 0
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then
Dim aCell As Range
For Each aCell In Target
updateACell aCell
Next aCell
Else
updateACell Target
End If
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hi,
I've created the below macro. By using the ChangeValue or a similar
call, I
need to adopt this macro in order to be able to change adjacent D date
value
to TODAY's date if I ever change any currency value on column C.
Can anyone suggest an alteration?.
=======================
Sub Macro1()
Range("C2").Select
ActiveCell.FormulaR1C1 = "27000"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("D3").Select
End Sub
=============================
TIA
Martyn




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22.05.2004





---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22.05.2004