delete cell contents if another cell is selected

[Bill Morris]

Hi

I have a column with the dropdown list option of just blank or "X" for each cell in that column.

I want to be able to make it work so that if a cell, say A3 has an "X" in it then all other cells in the column are cleared/blank - Except the Header Line in A1.

If another cell, say A12 is then selected ("X") then all except A12 and the header line are then cleared (including A3).

Probably a really easy way to do this.

Thanks

Bill

 

[Yongle]

Every time you click on a cell in range A2:A1000 cell values are cleared and cell clicked on given value "X"

Put this in SHEET module (right click on sheet tab \ View Code \ paste code into code window \ {alt}{F11} to go back to Excel

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    Dim rng As Range:       Set rng = Range("A2:A1000")
        
    If Not Intersect(Target, rng) Is Nothing Then
        rng.ClearContents
        Target = "X"
    End If
End Sub

 

[Bill Morris]

Every time you click on a cell in range A2:A1000 cell values are cleared and cell clicked on given value "X"

Put this in SHEET module (right click on sheet tab \ View Code \ paste code into code window \ {alt}{F11} to go back to Excel

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    Dim rng As Range:       Set rng = Range("A2:A1000")
        
    If Not Intersect(Target, rng) Is Nothing Then
        rng.ClearContents
        Target = "X"
    End If
End Sub

Awesome! Thanks for the quick reply to this. Works a treat!