CODEWITHSUNDEEP
vbaexcel
Urho
Urho

Reputation: 11

How to test if a Target cell is in a range of multiple cells: Excel VBA

I got the below code online, and now I'm trying to edit to make it work for multiple cells like, say, the whole "I" column. How do I do this?

Because I didn't write the original code, have no proper clue as to how to edit it. I understand it's the Target.Address we want to meddle with, but whatever logic I try to use only results in a debugger error.

Dim Oldvalue As String
Dim Newvalue As String

On Error GoTo Exitsub
If Target.Address = "$I$1" Then
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
    Else: If Target.Value = "" Then GoTo Exitsub Else
        Application.EnableEvents = False
        Newvalue = Target.Value
        Application.Undo
        Oldvalue = Target.Value
        If Oldvalue = "" Then
            Target.Value = Newvalue
        Else
            Target.Value = Oldvalue & ", " & Newvalue
        End If
    End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

Upvotes: 1

Views: 3718

Answers (1)

Wolfie
Wolfie

Reputation: 30047

A naïve approach would be to loop though all cells in column I and do the check against the Target. The simpler method is to use Intersect.

' Set up the range you're interesting in
Dim myRange As Range
Set myRange = ThisWorkbook.Sheets("Sheet1").Range("I1:I100")

' Replacing "If Target.Address = "$I$1" Then"
If Not Intersect(myRange, Target) Is Nothing Then
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
        ...
    End If
End If

Note though, it might be worth finding a different approach for whatever this task is. Using Application.Undo is probably going to cause issues as some point as it's a blind call to undo without knowing what that will actually do!

Upvotes: 1

Related Questions