run macro using one of a number of cells Solved/Closed

Question

Hello,
I'm tring to run Macro1 when any one cell (and no others) between A35 and A600 is activated I have Managed to either do a single cell, as below:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Column = 1 And Target.Row = 35 Then Macro1
End Sub


Or the whole column, as below

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 And Target.Count = 1 Then Call Macro1
End Sub

But I need to run it from any of the cells between A35 & A600.

Does anybody know how to achieve this please?

Thanks in advance

Craig

WutUp WutUp · Accepted Answer

See if this gets you in the right direction. Hope it helps!


Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyRange As Range
Dim IntersectRange As Range

Set MyRange = Range("A35:A600")

Set IntersectRange = Intersect(Target, MyRange)

If IntersectRange Is Nothing Then
Exit Sub
Else: Call Macro1
End If


End Sub

craigo · Answer

Hi,
Thanks for your reply, I've tried the code you gave me but it doesn't work, it doesn't come up with any errors either, nothing happens at all, any ideas?

Craig

craigo · Answer

Thats absolutely perfect, thank you very much. 
The next problem I've encountered is with the macro itself. I'm trying to move the active cell over to the right one cell and copy that and the further 10 cells to the right. I've managed to move the activated cell with the offset command but can't get it to copy all the cells?

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.Unprotect
    ActiveCell.Offset(0, 1).Select
    Selection.Copy
    Range("B2").Select
    ActiveSheet.Paste
    Rows("2:2").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("3:3").Select
    Selection.Copy
    Rows("2:2").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

craigo · Answer

You're an absolute star, thank you so much, it works perfectly, very grateful :-)

Run macro using one of a number of cells

4 responses

Similar discussions

Subscribe To Our Newsletter!