I'm using Excel 2003

Hi,

What I'm trying to achieve is that when I change a cell in column A, let's
say A5 (but the code should work for any cell in column A), into a certain
pre-set value. e.g. "XX", "Y" and "BBB" should trigger the code, but any
other input should not trigger the code.

When I enter one of those pre-set values, the cell of the same row in column
H (so H5 in this case) should equal the entries that I'm going to enter in to
column F (F5 in this case)

When I remove these pre-set values entered in column A, the entire process
should be undone.

Thanks in advance,

Paul.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:A" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

If .Value = "XX" Or .Value = "Y" Or .Value = "BBB" Then

.Offset(0, 7).FormulaR1C1 = "=RC[-2]"
Else

.Offset(0, 7).Value = ""
End If
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

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Paul Tikken" <(E-Mail Removed)> wrote in message
news:656F5D12-1C59-4671-A7E5-(E-Mail Removed)...
> I'm using Excel 2003
>
> Hi,
>
> What I'm trying to achieve is that when I change a cell in column A, let's
> say A5 (but the code should work for any cell in column A), into a certain
> pre-set value. e.g. "XX", "Y" and "BBB" should trigger the code, but any
> other input should not trigger the code.
>
> When I enter one of those pre-set values, the cell of the same row in
> column
> H (so H5 in this case) should equal the entries that I'm going to enter in
> to
> column F (F5 in this case)
>
> When I remove these pre-set values entered in column A, the entire process
> should be undone.
>
> Thanks in advance,
>
> Paul.
>

Hi Paul

Put this code in the code sheet for the desired sheet and test it. Note that
this macro is case sensitive, but it can be changed!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isec As Range
Set isec = Intersect(Columns("A"), Target)
If Not isec Is Nothing Then
If Target = "" Then
Target.Offset(0, 7) = ""
Else
Select Case Target.Value
Case Is = "XX"
Target.Offset(0, 7).Formula = "=F" & Target.Row
Case Is = "Y"
Target.Offset(0, 7).Formula = "=F" & Target.Row
Case Is = "BBB"
Target.Offset(0, 7).Formula = "=F" & Target.Row
End Select
End If
End If
End Sub

Regards,

Per

"Paul Tikken" <(E-Mail Removed)> skrev i en meddelelse
news:656F5D12-1C59-4671-A7E5-(E-Mail Removed)...
> I'm using Excel 2003
>
> Hi,
>
> What I'm trying to achieve is that when I change a cell in column A, let's
> say A5 (but the code should work for any cell in column A), into a certain
> pre-set value. e.g. "XX", "Y" and "BBB" should trigger the code, but any
> other input should not trigger the code.
>
> When I enter one of those pre-set values, the cell of the same row in
> column
> H (so H5 in this case) should equal the entries that I'm going to enter in
> to
> column F (F5 in this case)
>
> When I remove these pre-set values entered in column A, the entire process
> should be undone.
>
> Thanks in advance,
>
> Paul.
>

Per,

It's not working, no response at all.

Any ideas?

Paul

"Per Jessen" wrote:

> Hi Paul
>
> Put this code in the code sheet for the desired sheet and test it. Note that
> this macro is case sensitive, but it can be changed!
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim isec As Range
> Set isec = Intersect(Columns("A"), Target)
> If Not isec Is Nothing Then
> If Target = "" Then
> Target.Offset(0, 7) = ""
> Else
> Select Case Target.Value
> Case Is = "XX"
> Target.Offset(0, 7).Formula = "=F" & Target.Row
> Case Is = "Y"
> Target.Offset(0, 7).Formula = "=F" & Target.Row
> Case Is = "BBB"
> Target.Offset(0, 7).Formula = "=F" & Target.Row
> End Select
> End If
> End If
> End Sub
>
> Regards,
>
> Per
>
> "Paul Tikken" <(E-Mail Removed)> skrev i en meddelelse
> news:656F5D12-1C59-4671-A7E5-(E-Mail Removed)...
> > I'm using Excel 2003
> >
> > Hi,
> >
> > What I'm trying to achieve is that when I change a cell in column A, let's
> > say A5 (but the code should work for any cell in column A), into a certain
> > pre-set value. e.g. "XX", "Y" and "BBB" should trigger the code, but any
> > other input should not trigger the code.
> >
> > When I enter one of those pre-set values, the cell of the same row in
> > column
> > H (so H5 in this case) should equal the entries that I'm going to enter in
> > to
> > column F (F5 in this case)
> >
> > When I remove these pre-set values entered in column A, the entire process
> > should be undone.
> >
> > Thanks in advance,
> >
> > Paul.
> >
>
>
>

Bob,

It's not working, no response at all

Any ideas?

Paul

"Bob Phillips" wrote:

> Private Sub Worksheet_Change(ByVal Target As Range)
> Const WS_RANGE As String = "A:A" '<== change to suit
>
> On Error GoTo ws_exit
> Application.EnableEvents = False
>
> If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> With Target
>
> If .Value = "XX" Or .Value = "Y" Or .Value = "BBB" Then
>
> .Offset(0, 7).FormulaR1C1 = "=RC[-2]"
> Else
>
> .Offset(0, 7).Value = ""
> End If
> 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
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> "Paul Tikken" <(E-Mail Removed)> wrote in message
> news:656F5D12-1C59-4671-A7E5-(E-Mail Removed)...
> > I'm using Excel 2003
> >
> > Hi,
> >
> > What I'm trying to achieve is that when I change a cell in column A, let's
> > say A5 (but the code should work for any cell in column A), into a certain
> > pre-set value. e.g. "XX", "Y" and "BBB" should trigger the code, but any
> > other input should not trigger the code.
> >
> > When I enter one of those pre-set values, the cell of the same row in
> > column
> > H (so H5 in this case) should equal the entries that I'm going to enter in
> > to
> > column F (F5 in this case)
> >
> > When I remove these pre-set values entered in column A, the entire process
> > should be undone.
> >
> > Thanks in advance,
> >
> > Paul.
> >
>
>
>