VBA Excel Target & Intersect
Posted on 2009-12-29
I have a little proc that runs on a change in the worksheet in front of it, but it's behaving badly.
The proc allows the user to place a checkmark in Column A when clicking a cell in that column or moving the cursor there via the keyboard. There are a couple of problems with it:
1) If the user selects the entire row, the contents are deleted.
2) It does not play well with Filters.
If you see a glaring issue or have a better way to provide this ability, let me know.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim p As Integer
Dim r As Range
p = Cells(Rows.count, "B").End(xlUp).Row
Set r = Range("A4:A" & p)
If Not Intersect(Target, r) Is Nothing Then
If Target.Text = "a" Then
Target.Value = ""
Target.Value = "a"