Column value

[Paul Tikken]

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 Phillips]

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)

 

[Per Jessen]

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]

Per,

It's not working, no response at all.

Any ideas?

Paul

 

[Paul Tikken]

Bob,

It's not working, no response at all

Any ideas?

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)

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.