Selection Address
 

XL2003
How can I use a selection address from sheet1 as a range on sheet 2?

I want to select a range of cells on sheet1, then click on a command button
and have the same range selected on sheet2 so I can run another procedure on
that range.

I have this code that works on a selection from the same sheet.
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''
Private Sub cmd1_Click()

For Each c In Selection
If c.Font.ColorIndex = 3 Then ms = ms + c
Next
For Each c In Selection
If c.Interior.ColorIndex = 38 Then ms = ms * 2
Next
For Each c In Selection
If c.Interior.ColorIndex = 3 Then ms = ms * 3
Next
MsgBox ms
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''

Selection Address
 

Hi,

Assume that (say) sheet 1 is the active sheet and you have a range of cells
selected. This will loop through the same range of cells on sheet 2. Sheet 1
must remain the active sheet.

Sub Sonic()
For Each c In Sheets("Sheet2").Range(Selection.Address)
MsgBox c.Value
Next
End Sub


Mike


"Gizmo" wrote:

XL2003
How can I use a selection address from sheet1 as a range on sheet 2?

I want to select a range of cells on sheet1, then click on a command button
and have the same range selected on sheet2 so I can run another procedure on
that range.

I have this code that works on a selection from the same sheet.
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''
Private Sub cmd1_Click()

For Each c In Selection
If c.Font.ColorIndex = 3 Then ms = ms + c
Next
For Each c In Selection
If c.Interior.ColorIndex = 38 Then ms = ms * 2
Next
For Each c In Selection
If c.Interior.ColorIndex = 3 Then ms = ms * 3
Next
MsgBox ms
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''