range problem

[VBAva]

hi all,

i have a little problem, probably just syntax error or something but i cant figure this out.
i want to select a few adjacent cells in a row.
i cant use xlToRight as it effects some formatting later on.
so what i want is something like this ( but that works)

Sub Macro4()
' Macro4 Macro
' Macro recorded 29.08.2003
Dim ColumnOffset As Integer
ColumnOffset = 3 'Not always 3
Range("G14&quot.Select
Range(Selection, Selection.End(ActiveCell.Offset(0, ColumnOffset))).Select 'problem with this line
With Selection.Interior 'Interior colour black
.ColorIndex = 1
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2 'White Text
End Sub


the problem is on the line
Range(Selection, Selection.End(ActiveCell.Offset(0, ColumnOffset))).Select

im not too sure exactly how to write this
any help or suggestions would be appreciated. Thanks

 

[Bowers74]

Sub Macro4()
' Macro4 Macro
' Macro recorded 29.08.2003
    Dim ColumnOffset As Integer

ColumnOffset = Inputbox "Enter column offset."

    Range("G14").Select

Range(Activecell, Activecell.Offset(0,ColumnOffset)).Select

    With Selection.Interior             'Interior colour black
        .ColorIndex = 1
        .Pattern = xlSolid
    End With
    Selection.Font.ColorIndex = 2       'White Text
End Sub

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[VBAva]

hahahahaha
oh my god, i feel so dumb!

its too early in the morning for thinking.

thank you

 

[Bowers74]

One last tip:

Try not to use Selection that often. Use ActiveCell.

The problem is that one time the selection might be a shape, chart or picture and then your code will cuase an error.

If you insist on using Selection in your code (or if you absolutely have to, then add this line to the top of your code:

If Not TypeName(Selection) = "Range" then Exit Sub

/color]

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[VBAva]

Thanks for the advice,ill keep it in mind