Event target range

**bobkap** · 09-03-2012, 02:34 AM

I have a spreadsheet setup to run a macro every time there is a change in the worksheet. BUT, I only want this macro to be run automatically if a specific range of cells are changed. I am guessing that the "target" range includes the whole worksheet by default. Is there some way that I can address that so that the macro only runs in that specific absolute range?

**Rick Rothstein** · 09-03-2012, 07:35 AM

I moved your post to the Excel Help sub-forum where it will be exposed to more people capable of answering your question.

**Rick Rothstein** · 09-03-2012, 07:46 AM

Originally Posted by bobkap

I I am guessing that the "target" range includes the whole worksheet by default.

Target is the range of cells that were changed (assuming we are talking about a Change event), so you can test if that range has any cells you might be interested in. Normally, you are probably expecting the user to change one cell at a time, so you could do this (where I have made up a range of interest, C3:F10, because you didn't tell us what it actually is)...

Code:

If Intersect(Target, Range("C3:F10")) Is Nothing Then Exit Sub

However, it is possible for a user to paste in values where some or all of the values intersect the area of interest. How to handle this kind of depends on what any remaining code in the Change event, or what your macro does with the cells that were change. If the code in either location needs to work with the cells that actually changed, then a loop might be appropriate...

Code:

Dim Cell As Range
If Intersect(Target, Range("C3:F10")) Is Nothing Then Exit Sub
' Since we got here, one or more cells in Target intersected with C3:F10, so...
For Each Cell In Intersect(Target, Range("C3:F10"))
    ' The Cell variable is a reference to a cell that changed, so your code can do whatever is need to or with it
Next

**Rasm** · 09-13-2012, 05:34 AM

Below is example of what I use - I keep all my default settings in a sheet where the operator can toggle between valid selections for a given variable. In this example what delimiter is used in ASCII files that can be imported, parsed and killed

This just part of code --- so add the rest as you see fit

Hope this helps

Thanks Rasm

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 3 Then
        With ActiveSheet
             If .Cells(Target.Row, 1).Value = "FileDelimiter" Then
                If .Cells(Target.Row, Target.Column).Value = "CSV" Then
                        .Cells(Target.Row, Target.Column).Value = "TAB"
                    Else
                        .Cells(Target.Row, Target.Column).Value = "CSV"
                End If
            End If
     End With