Changes in specific range shall trigger code Hi fellows, I have a hopefully rath

Question

Changes in specific range shall trigger code

Hi fellows,

I have a hopefully rather simple question for you guys. My starting point is a range which is referenced by a Range Name. Now I have some code that shall be triggered once rows 3 to 7 (=last row) within this range were altered. I believe the only option to do so is to make use of the Worksheet Change event. However, the code takes some time and therefore I would like to run the code only if the respective range was changed. I believe the most efficient form is to use an If-Statement for this code. So far I have tried the following code which does not work because of improper syntax I guess:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range(Cells([NamedRange].Row + 2, 3), Cells([NamedRange].Row + [NamedRange].Rows.Count, 16))) Then

Run Code

End if

End Sub

Thanks !

Explanation / Answer

Hi

Try:

'=========>>
Option Explicit

'---------->>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim firstRow As Long, lastRow As Long

firstRow = Range("myRangeName").Row
lastRow = firstRow + Range("myRangeName").Rows.Count

Set Rng = Range(Cells(firstRow +2, 3), Cells(lastRow+1 , 16))
If Not Intersect(Target, Rng) Is Nothing Then
'\ Your code
End If
End Sub

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.