Loop through Range

[St Jimmy]

When using Worksheet_Change event, I see that if the user pastes a range of cells at once the values are input as an array. How can I loop through the values of the array, but also catch if a single cell is changed?

What I have right now, this works for single cell changes but not pasted lists:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Target.Value2 = Function.blahblahblah

I'm trying to change it to this, but I cant get a handle on the formatting:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim tempValue As Variant

For Each TempValue in Target
     Target.Value2 = Function.blahblahblah
Next TempValue

Any help with syntax would be appreciated!

 

[St Jimmy]

Thank you Fluff, this worked.
Today, you are my hero.

 

[Fluff]

You're welcome & thanks for the feedback