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="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
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="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
Last edited: