copying a cell to another cell automatically

[Amit]

Hi,
I'm trying to set up my worksheet so that if the value in column E is
changed to 100%, the value in column A of that row automatically changes to
100, regardless of its original value. At the same time, I don't want to
lock down column A - the user can enter any value in here when E is not
100%. How do I go about doing this? Do I need to write a macro?
Thanks!
Amit

[richardreye]

try this code placed on the sheet that you want this to happen on. That is, in the Visual Basic Editor, select the Microsoft Excel Objects folder and double click on Sheet1 if it is Sheet1 that needs this event to occur.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo Finish

If Target.Column = 5 And Target.Value = 1 Then _
Cells(Target.Row, 1).Value = Format(1, "0%")

Finish:

Application.EnableEvents = True

End Sub

[David Jessop]

Hi,

You'll have to do this as a little macro. Go into the VB editor and add the
following into the relevant Sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 And Target = 100 Then
Cells(Target.Row, 1) = 100
End If
End Sub

You might want to check for ABS (target - 100) < some small number rather
than equality.

HTH, David Jessop

"Amit" wrote:

> Hi,
> I'm trying to set up my worksheet so that if the value in column E is
> changed to 100%, the value in column A of that row automatically changes to
> 100, regardless of its original value. At the same time, I don't want to
> lock down column A - the user can enter any value in here when E is not
> 100%. How do I go about doing this? Do I need to write a macro?
> Thanks!
> Amit
>
>
>

[Amit]

Awesome, thanks!


"David Jessop" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> You'll have to do this as a little macro. Go into the VB editor and add
the
> following into the relevant Sheet.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Column = 5 And Target = 100 Then
> Cells(Target.Row, 1) = 100
> End If
> End Sub
>
> You might want to check for ABS (target - 100) < some small number rather
> than equality.
>
> HTH, David Jessop
>
> "Amit" wrote:
>
> > Hi,
> > I'm trying to set up my worksheet so that if the value in column E is
> > changed to 100%, the value in column A of that row automatically changes
to
> > 100, regardless of its original value. At the same time, I don't want to
> > lock down column A - the user can enter any value in here when E is not
> > 100%. How do I go about doing this? Do I need to write a macro?
> > Thanks!
> > Amit
> >
> >
> >