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

RuiFlora

Board Regular
Joined
Feb 28, 2014
Messages
58
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
 
Last edited:

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Forum statistics

Threads
1,223,240
Messages
6,170,951
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top