How to set Range based off Target Range?

I want to do something like this:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim affectedRange As Range
affectedRange = Target + 6 columns
End Sub


Here's an example of what I mean by "+ 6 columns": If the target range was C4, then affectedRange should be C4:I4. If target was C4:E8 the affectedRange should be C4:L8.

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)

Dim affectedRange As Range
   
Set affectedRange = Target.Resize(1,6)

End Sub

---

Thanks! Although won't your code always produce a range with 1 row and 6 columns? The way that seems to work the way I wanted is this:

Set affectedRange = Target.Resize(, Target.Columns.Count + 6)

Yes - one row. Use your change

Quote:

---

If target was C4:E8 the affectedRange should be C4:L8.

---

I think this should be C4:K8

You're right. I don't know the alphabet, apparently.

Quote:

---

Originally Posted by Paul_Hossler

I think this should be C4:K8

---

That's OK

I can't count