CODEWITHSUNDEEP
excelvba
John Adam1
John Adam1

Reputation: 53

Excel: Delete cell content if another cell is clicked

I have the following code that set an "x" on a cell every time I click on it in the range ("C3:C40)".

The problem is it is putting the "x" in multiple cells and I want only one "x" in the range, only the clicked one.

How could I change this code to make it?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rInt As Range
    Dim rCell As Range

    Set rInt = Intersect(Target, Range("C3:C40"))
    If Not rInt Is Nothing Then
        For Each rCell In rInt
            rCell.Value = "x"
        Next
    End If
    Set rInt = Nothing
    Set rCell = Nothing
End Sub

Thanks

Upvotes: 1

Views: 879

Answers (3)

Cyril
Cyril

Reputation: 6829

Elaborating on my comment to the original post:

Dim r as Long, c as Long
r = ActiveCell.Row 
c = ActiveCell.Column
Range("C3:C40").ClearContents
Cells(r,c).Value="x"

Upvotes: 1

Nicholas Kemp
Nicholas Kemp

Reputation: 337

Added a loop that checks for any value in the specified range and sets the value to a clear string (nothing) if found. Then, the code you've been using puts the "x" on the desired cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rInt As Range
Dim rCell As Range
Dim c As Variant

Set rInt = Intersect(Target, Range("C3:C40"))
If Not rInt Is Nothing Then
    For Each c In Range("C3:C40").Cells 'Erase loop
        If c.Value <> "" Then
            c.Value = ""
        End If
    Next

    For Each rCell In rInt
        rCell.Value = "x"
    Next
End If
Set rInt = Nothing
Set rCell = Nothing
End Sub

Upvotes: 1

user8733192
user8733192

Reputation:

does the range value needs to be empty if it isnt x?

   Private Sub Worksheet_SelectionChange(ByVal 
   Target As Range)
    Dim rInt As Range

    Set rInt = Intersect(Target, Range("C3:C40"))
    If Not rInt Is Nothing Then
        rlnt.value=""
        ActiveCell.Value = "x"
    End If
    End Sub

Upvotes: 0

Related Questions