Selecting simple range relative to the active cell.

[Guest]

I need to have my macro select the 9 cells starting with the active cell in
column B and extending 8 cells to the right. I am doing this as a custom
"copy range" funtion in a macro. I do not want an absolute reference for the
range. It should be relative to any ACTIVE cell in column B.

If there is no ACTIVE cell in column B, then I would like to return an error
message "Wrong Column Selected" with and "O.K." button.

 

[Guest]

Give this a try...

Sub SelectRange()

If ActiveCell.Column = 2 Then
ActiveCell.Resize(1, 9).Select
Else
MsgBox "Sorry. You are in the wrong column...", _
vbInformation, "Wrong Column"
End If
End Sub

 

[Steve Yandl]

Dim newRng As Range
If Not ActiveCell.Column = 2 Then
MsgBox "You selected a cell in the wrong column"
Else
rowStr = CStr(ActiveCell.Row)
Set newRng = Range("$B$" & rowStr & ":$J$" & rowStr)
newRng.Select
End If

Steve