activecell - "x" in adjacent cell

  • Thread starter Thread starter johnT
  • Start date Start date
J

johnT

is there a simple macro that will do the following:

if i select cell A2 i would like it to put an "x" in B2 and
remove an "x" (if there is one) in B3. if i select cell A3
put an "x" in B3 and remove an "x" (if present) in B2

thanks in advance.
 
Try this in the sheet module (rightclick the sheet tab & "View Code" to
access it) ;

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
ActiveCell.Offset(0, 2) = ""
ActiveCell.Offset(0, 1) = "X"
End If
End Sub

HTH,
Andy
 
Hi John,

I expect you only have an "x" or nothing in column B.
but will check for an "x" like you indicated. VBA is
case sensitive so will enclose the value in UCASE(value)

The following is a selection change event macro, install by right click on
the sheet tab, view code, and plop the code in under the
option explicit statement.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
On Error Resume Next 'MUST reenable events...
Application.EnableEvents = False
If UCase(Target.Offset(0, 1).Value) = "X" Then
Target.Offset.Offset(0, 1).ClearContents
Else
Target.Offset(0, 1).Value = "x"
End If
Application.EnableEvents = True
End Sub

More information on Event macros see
http://www.mvps.org/dmcritchie/excel/event.htm
 
Okay mine is incorrect I didn't read the last phrase of your sentence
which clarified the first part. And I presume only the cells mentioned
not additional cells in the columns for other selections would get
modified.
 
One way:

Put this in your worksheet code module:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
With Target(1)
If Not Intersect(.Cells, Range("A2:A3")) Is Nothing Then
Range("B2:B3").ClearContents
.Offset(, 1).Value = "x"
End If
End With
End Sub
 
Back
Top