Run macro using one of a number of cells

Solved/Closed
craigo Posts 19 Registration date Friday February 27, 2009 Status Member Last seen April 4, 2009 - Feb 28, 2009 at 08:17 AM
 sam - Aug 19, 2009 at 07:57 AM
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

4 replies

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
3
craigo Posts 19 Registration date Friday February 27, 2009 Status Member Last seen April 4, 2009 1
Mar 2, 2009 at 12:36 PM
You're an absolute star, thank you so much, it works perfectly, very grateful :-)
1
craigo Posts 19 Registration date Friday February 27, 2009 Status Member Last seen April 4, 2009 1
Mar 1, 2009 at 05:53 AM
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
0
I took "Selection" out of....Private Sub Worksheet_Change(ByVal Target As Range).

If you are wanting to (mouse) click on a cell in the range, put it back in like you had it.
Sorry, I thougt you wanted to enter a value in a cell.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
0
craigo Posts 19 Registration date Friday February 27, 2009 Status Member Last seen April 4, 2009 1
Mar 1, 2009 at 01:40 PM
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
0
Does this work for you?

Sub Macro1()

Dim j
j = 2

ActiveSheet.Unprotect
Cells(j, 2) = ActiveCell.Offset(0, 1).Value
Cells(j, 3) = ActiveCell.Offset(0, 2).Value
Cells(j, 4) = ActiveCell.Offset(0, 3).Value
Cells(j, 5) = ActiveCell.Offset(0, 4).Value
Cells(j, 6) = ActiveCell.Offset(0, 5).Value
Cells(j, 7) = ActiveCell.Offset(0, 6).Value
Cells(j, 8) = ActiveCell.Offset(0, 7).Value
Cells(j, 9) = ActiveCell.Offset(0, 8).Value
Cells(j, 10) = ActiveCell.Offset(0, 9).Value
Cells(j, 11) = ActiveCell.Offset(0, 10).Value
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
0
sam > WutUp WutUp
Aug 19, 2009 at 07:57 AM
Hi,

I tried from all your list of codes. I could not solve a problem.

I need a code were any cell in the sheet if i change the existing value the color should change in that Cell.

Like if A1 cell already have a data 20 and it is in green color. When i change the value of A1 cell to 25 it should change to Red color.

I tried

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 Then Target.Interior.ColorIndex = 3
End Sub

this works only on the first row how can it work on the entire sheet, Can u please help me
0