is_utf8:0,
 
Search : in
By :

Run macro using one of a number of cells

Last answer on Aug 19, 2009 12:57:07 pm BST craigo, on Feb 28, 2009 1:17:31 pm GMT 
 Report this message to moderators

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

Configuration: Windows Vista
Internet Explorer 7.0

Best answers for « run macro using one of a number of cells » in :
VBA macro to move to the cell 'underneath' ShowVBA macro to move to the cell 'underneath' Issue Solution Issue If you need need VBA macro to move to the cell 'underneath' the particular button that is being pressed, series of buttons down the page, each will run the same macro/do...
Excel Comparing cell A1 to entire A column in Sheet 2 ShowExcel Comparing cell A1 to entire A column in Sheet 2 Issue Solution Note Issue I have been trying to compare sheet1 A2 to sheet2 A2 through A500 and if it exists somewhere in sheet2's a col then copy that entire row to a new sheet....
SERIAL NUMBER- Trend Micro PC CILLIN ShowSERIAL NUMBER- Trend Micro PC CILLIN Explanation How to replace your old serial number by the new one? Explanation Go to registry by Menu Start > Run and type REGEDIT and click on “OK”. In the open window under « My computer...
Download USA Unlisted Cell Phone Numbers ShowHas you friend flown for America without telling you where he/she would settle? Dont panic! USA Unlisted Cell Phone Numbers is a program which can help you. By using this program, you simply need to know his/her phone number in order to find...
Ask your question Reply

1

WutUp WutUp, on Feb 28, 2009 5:26:21 pm GMT

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

   
Reply to WutUp WutUp

2

craigo, on Mar 1, 2009 10:53:03 am GMT

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

   
Reply to craigo

3

WutUp WutUp, on Mar 1, 2009 2:03:06 pm GMT
  • +1

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)

   
Reply to WutUp WutUp

4

craigo, on Mar 1, 2009 6:40:33 pm GMT

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

   
Reply to craigo

5

WutUp WutUp, on Mar 2, 2009 3:05:35 am GMT

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

   
Reply to WutUp WutUp

7

 sam, on Aug 19, 2009 12:57:07 pm BST

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

   
Reply to sam

6

craigo, on Mar 2, 2009 5:36:20 pm GMT
  • +1

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

   
Reply to craigo
Ask your question Reply
They need your help