VBA Add a value to several cells based on a click in another cell

[RuiFlora]

Hello. I know this could be a widely discussed question but I deeply looked for an answer and wasn't able to find the best one.
I am trying to make a code which fill the value "x" to a range of cells (by columns), based on a single click in one other cell. Moreover i want that one another click on the main cell with the "x" turns blank the other ones. It may sounds confusing so i put an example.

The code I made up so far is the following, but it is not working right:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim regionInt As Range
Dim regionCell As Range

Set region = Worksheets("regiontest").Range("region")
Set regionfield = Worksheets("regiontest").Range("regionfield")

Set regionInt = Intersect(Target, Sheet3.Range("region"))
Set regionCell = Intersect(Target, Sheet3.Range("regionfield"))


If Not regionInt Is Nothing Then
For Each regionCell In regionInt
If regionCell.Value = "" Then
regionCell.Value = "x"
ElseIf regionCell.Value = "x" Then
regionCell.Value = ""
End If
Next
Sheet3.Range("A1").Select
End If

Set regionInt = Nothing
Set regionCell = Nothing

End Sub


[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Region[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Africa[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]America[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Asia[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Europe[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In sum, the main goal is to click in front of "Region" and fill all the cells below (by column), And be able to invidividually remove one of them if necessary. And if we want to, click again in front of "region" to remove all of them.

Many thanks if someone can help me!

Best

 

[Snakehips]

RuiFlora,

I'm not sure how you are wanting to use your various named ranges but maybe this simple example will give you some ideas.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Row = 1 Then
Lr = Cells(Rows.Count, 1).End(xlUp).Row
If Target = "" Then
Target.Resize(Lr, 1) = "*"
Else: Target.Resize(Lr, 1) = ""
End If
Exit Sub
End If
Target = ""
End Sub

Hope that helps.

 

[RuiFlora]

RuiFlora,

I'm not sure how you are wanting to use your various named ranges but maybe this simple example will give you some ideas.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Row = 1 Then
Lr = Cells(Rows.Count, 1).End(xlUp).Row
If Target = "" Then
Target.Resize(Lr, 1) = "*"
Else: Target.Resize(Lr, 1) = ""
End If
Exit Sub
End If
Target = ""
End Sub

Hope that helps.

Thank you for your quick answer.

Maybe I have too much ranges named. The thing is, I have this table inserted between other ones. Therefore, I can't count till the end of the columns, thats why I have so much ranges. If i can handle this one, i'll just have to change the ranges names for other tables. What I want is to click in front of "region" and fill with "x" the cells below, ending in the end of the range.

Thanks

 

[Snakehips]

Please tell me how you have defined your ranges, relative to the column of countries.

 

[RuiFlora]

Please tell me how you have defined your ranges, relative to the column of countries.

I tested the code you sent me and it's almost there!

The range I want to click to set all the "x" is the line of "region" except the cell of region (B2:E2)
The range i want to fill and edit further if necessary is (B3:E5)

If i would be able to add "x" in the regions even after I remove them as well, it would be great!

Thank you very much

 

[Snakehips]

Try this.....

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("B1:E5")) Is Nothing Then Exit Sub
If Target.Row = 1 Then
If Target = "" Then
Target.Resize(5, 1) = "*"
Else: Target.Resize(5, 1) = ""
End If
Exit Sub
End If
If Target = "" Then
Target = "*"
Else
Target = ""
End If
End Sub

 

[RuiFlora]

Try this.....

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("B1:E5")) Is Nothing Then Exit Sub
If Target.Row = 1 Then
If Target = "" Then
Target.Resize(5, 1) = "*"
Else: Target.Resize(5, 1) = ""
End If
Exit Sub
End If
If Target = "" Then
Target = "*"
Else
Target = ""
End If
End Sub

Works like a charm! Many thanks