Column value
 

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.

Column value
 

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" wrote in message
...
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.

Column value
 

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" skrev i en meddelelse
...
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.

Column value
 

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" skrev i en meddelelse
...
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.

Column value
 

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" wrote in message
...
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.