Alter one cell value, in case another cell's value changed

M

myBasic

Hello everybody

I am new in Excel macro programming

Is there any ways to fill some words "remarks" into Cell B2 in Eecel worksheet, in case Cell A1 value has been modified. I don't know how to inplement the actual Excel macro code but using pseudocode to represent my meaning, say

in Cell B, fill formula:

= If cells('A1').value != originalValue The
cells('B1').value = "Cell A1 has been modified on " & Date(
End I

Kindly tell how to convert the above meaning into extual Excel macro code

Thanks & regards
 
B

Bob Phillips

Here is some code

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
With Target
.Offset(0, 1).Value = "Cell " & Target.Address(False,False) & _
" has been modified on " & _
Format(Date, "dd mmm yyyy")
End With
End If

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)

myBasic said:
Hello everybody,

I am new in Excel macro programming.

Is there any ways to fill some words "remarks" into Cell B2 in Eecel
Click to expand...
worksheet, in case Cell A1 value has been modified. I don't know how to
inplement the actual Excel macro code but using pseudocode to represent my
meaning, say:
 
M

myBasic

Hi Bob

Your code works fine, thanks.

But how to apply the code to following rows, ie, let's illustrate with the code

If Not Intersect(Target, Me.Range("A1")) Then change Cell A2 value.
If Not Intersect(Target, Me.Range("B1")) Then change Cell B2 value.
If Not Intersect(Target, Me.Range("C1")) Then change Cell C2 value.
If Not Intersect(Target, Me.Range("D1")) Then change Cell D2 value..
..

thanks




----- Bob Phillips wrote: ----

Here is some cod

Private Sub Worksheet_Change(ByVal Target As Range

On Error GoTo ws_exit
Application.EnableEvents = Fals
If Not Intersect(Target, Me.Range("A1")) Is Nothing The
With Targe
.Offset(0, 1).Value = "Cell " & Target.Address(False,False) &
" has been modified on " &
Format(Date, "dd mmm yyyy"
End Wit
End I

ws_exit
Application.EnableEvents = Tru
End Su

'This is worksheet event code, which means that it needs to b
'placed in the appropriate worksheet code module, not a standar
'code module. To do this, right-click on the sheet tab, selec
'the View Code option from the menu, and paste the code in



--

HT

Bob Phillip
... looking out across Poole Harbour to the Purbeck
(remove nothere from the email address if mailing direct

myBasic said:
Hello everybody
Click to expand...
worksheet, in case Cell A1 value has been modified. I don't know how t
inplement the actual Excel macro code but using pseudocode to represent m
meaning, say
 
B

Bob Phillips

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1,B1,C1,D1")) Is Nothing Then
With Target
.Offset(1, 0).Value = "Cell " & Target.Address(False, False) & _
" has been modified on " & _
Format(Date, "dd mmm yyyy")
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

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

myBasic said:
Hi Bob,

Your code works fine, thanks.

But how to apply the code to following rows, ie, let's illustrate with the code.

If Not Intersect(Target, Me.Range("A1")) Then change Cell A2 value..
If Not Intersect(Target, Me.Range("B1")) Then change Cell B2 value..
If Not Intersect(Target, Me.Range("C1")) Then change Cell C2 value..
If Not Intersect(Target, Me.Range("D1")) Then change Cell D2 value..
...

thanks.







----- Bob Phillips wrote: -----

Here is some code

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
With Target
.Offset(0, 1).Value = "Cell " &
Click to expand...
Target.Address(False,False) & _
 
M

myBasic

It works fine, thank you, Bob


----- Bob Phillips wrote: ----

Private Sub Worksheet_Change(ByVal Target As Range

On Error GoTo ws_exit
Application.EnableEvents = Fals
If Not Intersect(Target, Me.Range("A1,B1,C1,D1")) Is Nothing The
With Targe
.Offset(1, 0).Value = "Cell " & Target.Address(False, False) &
" has been modified on " &
Format(Date, "dd mmm yyyy"
End Wit
End I

ws_exit
Application.EnableEvents = Tru
End Su


--

HT

Bob Phillip
... looking out across Poole Harbour to the Purbeck
(remove nothere from the email address if mailing direct
 
B

Bob Phillips

It's a pleasure.

--

HTH

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