CODEWITHSUNDEEP
excelvbashapes
Branchs75
Branchs75

Reputation: 45

Changing the value of a cell via excel vba if that cell happens to be part of a non contiguous range

I have seven cells "N13", "D17", "H17", "L17", "P17", "T17" and "X17". That are all empty. I have several shapes that will have macro's attached to them. The macro I need help writing should check to see if the currently selected cell is part of that seven cell range. If it is part of the seven cell range then the macro attached to the shape will simply place a number in that cell. So for example: I have selected cell L17, it is active, I click on the square shape and it changes the value of cell L17 to 4. (Number of sides of the shape.) If I have selected cell M17 and click the square shape it doesn't add 4 to the cell, it simply does nothing. The worksheet this is happening on is called "Main". I had tried a nested set of OR statements but this didn't work and I think I need to do some form of an intersect but I am unsure how to do this.

Sub Three_Side()
' Three_Side Macro

    Sheets("Main").Select
    If (ActiveCell.Row = 13 And ActiveCell.Column = 14) Or _
        (ActiveCell.Row = 17 And ActiveCell.Column = 4) Or _
        (ActiveCell.Row = 17 And ActiveCell.Column = 8) Or _
        (ActiveCell.Row = 17 And ActiveCell.Column = 12) Or _
        (ActiveCell.Row = 17 And ActiveCell.Column = 16) Or _
        (ActiveCell.Row = 17 And ActiveCell.Column = 20) Or _
        (ActiveCell.Row = 17 And ActiveCell.Column = 24) Then
        ActiveCell = 3
        Range("AD1") = Range("AD1") + 3
    End If

End Sub

Upvotes: 2

Views: 45

Answers (1)

BigBen
BigBen

Reputation: 50162

You were on the right track with Intersect.

Dim checkRng as Range
Set checkRng = Intersect(ActiveCell, Range("N13,D17,H17,L17,P17,T17,X17"))

If Not checkRng is Nothing Then
    ActiveCell.Value = 3
    Range("AD1").Value = Range("AD1").Value + 3
End If

Upvotes: 2

Related Questions