adopting a macro to a general situation.

M

Martyn

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
 
B

Bob Phillips

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)
 
T

Tushar Mehta

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
 
M

Martyn

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

Bob Phillips said:
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)

call,
Click to expand...
 
M

Martyn

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

Tushar Mehta said:
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
Click to expand...
 
Top