# Combine another intersection into sub

#### Mindlesh

##### Board Regular
How can I combine Intersect(Target, Range("StockQuotes
") into this sub so that I can apply the one sub to two separate ranges?
Code:
``````Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rCell As Range
Dim rChange As Range

On Error GoTo ErrHandler
Set rChange = Intersect(Target, Range("ExchangeRates[XRT]"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell > "" Then
With rCell.Offset(0, 1)
.Value = Now
.NumberFormat = "d-mmm"
End With
Else
rCell.Offset(0, 1).Clear
End If
Next
End If

ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub``````

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
How can I combine Intersect(Target, Range("StockQuotes
") into this sub so that I can apply the one sub to two separate ranges?
Code:
``````Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rCell As Range
Dim rChange As Range

On Error GoTo ErrHandler
[B][COLOR="#FF0000"]Set rChange = Intersect(Target, Range("ExchangeRates[XRT]"))[/COLOR][/B]
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell > "" Then
With rCell.Offset(0, 1)
.Value = Now
.NumberFormat = "d-mmm"
End With
Else
rCell.Offset(0, 1).Clear
End If
Next
End If

ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub``````
Try changing the highlighted line of code to this...
Code:
``````[table="width: 500"]
[tr]
[td]Set rChange = Intersect(Target, Union(Range("ExchangeRates[XRT]"), Range("StockQuotes[Quote]")))[/td]
[/tr]
[/table]``````

Thank you; I figured there must be an easy answer.

Replies
7
Views
353
Replies
5
Views
166
Replies
14
Views
437
Replies
3
Views
556
Replies
2
Views
255

1,203,387
Messages
6,055,122
Members
444,763
Latest member
Jaapaap

### 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?

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