Intersect(ActiveCell) Combining Ranges and Target.Value

kivikatz

New Member
Joined
Sep 12, 2020
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Hi All. Is it possible to combine the following two If Not Intersect statements into one statement? What I want to do is when I click on a cell in column B (1st If Not Intersect), it also activates the corresponding cell in column N (2nd If Not Intersect). For example, if click on B50 (which is inserted into "Symbol"), it will also activate N50 (which is inserted into "DateInput").

Hope you understand what I am trying to do. Thanks.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(ActiveCell, Range("B10:B110")) Is Nothing Then
Sheet311.Range("Symbol") = Target.Value
End If

If Not Intersect(ActiveCell, Range("N10:N110")) Is Nothing Then
Sheet311.Range("DateInput") = Target.Value - 1
End If

End Sub
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,553
Office Version
  1. 365
Platform
  1. Windows
Just have the first IF block select the same row in column N, which will trigger the code to run again, i.e.
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.CountLarge > 1 Then Exit Sub

If Not Intersect(Target, Range("B10:B110")) Is Nothing Then
    Sheet311.Range("Symbol") = Target.Value
    Cells(Target.Row, "N").Select
End If

If Not Intersect(Target, Range("N10:N110")) Is Nothing Then
    Sheet311.Range("DateInput") = Target.Value - 1
End If

End Sub
Also note. Typically in these kind of event procedures, you do not use ActiveCell, but rather use Target (which is the cell that is selected that activates the code to run).
 
Solution

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,553
Office Version
  1. 365
Platform
  1. Windows
You are weclome.
Glad I was able to help!
:)
 

Forum statistics

Threads
1,144,697
Messages
5,725,814
Members
422,643
Latest member
elwayfan446

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
Top