macro restricted to one column

D

davemon

I have the following macro, but I would like to run only when a "w" is
entered in any cell in column A.
Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Value = "w" Then
Range("I" & ActiveCell.Row).Select
End If
End Sub

Thanks in advance for the help!!!
 
J

Jim May

In your Sheet module enter

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A:A"), Target) Is Nothing Then
If Target.Value = "w" Then
Range("I" & Target.Row).Select
End If
End If
End Sub
 
K

Kevin B

You need to change your if to the following condition:

If ActiveCell.Value = "w" And ActiveCell.Column = 1 Then

but you will have to turn MOVE SELECTION AFTER ENTER to off by clicking
TOOLS/OPTIONS, clicking the EDIT tab and turning off the checkbox.
 
D

Dave Peterson

Private Sub Worksheet_Change(ByVal Target As Range)
if target.cells.count > 1 then exit sub 'only one cell at a time
if intersect(target,me.range("a:a")) is nothing then exit sub

if lcase(target.value) = "w" then
me.cells(target.row,"I").select
end if
end sub
 
G

Gord Dibben

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value = "w" Then
Range("I" & n).Select
End If
End If
enditall:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP
 
Top