Run macro using one of a number of cells [Solved/Closed]

craigo 19 Posts Friday February 27, 2009Registration date April 4, 2009 Last seen - Feb 28, 2009 at 08:17 AM - Latest reply:  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
See more 

7 replies

Best answer
WutUp WutUp - Feb 28, 2009 at 12:26 PM
3
Thank you
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

Thank you, WutUp WutUp 3

Something to say? Add comment

CCM has helped 1700 users this month

craigo 19 Posts Friday February 27, 2009Registration date April 4, 2009 Last seen - Mar 2, 2009 at 12:36 PM
1
Thank you
You're an absolute star, thank you so much, it works perfectly, very grateful :-)
craigo 19 Posts Friday February 27, 2009Registration date April 4, 2009 Last seen - Mar 1, 2009 at 05:53 AM
0
Thank you
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
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)
craigo 19 Posts Friday February 27, 2009Registration date April 4, 2009 Last seen - Mar 1, 2009 at 01:40 PM
0
Thank you
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
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
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