Current Selected Range

Hi,

I'm sure this is so simple - but i cant get my head round it!

All i want is the macro to run on the current selected range (just format some cells) but i dont want any message boxes the user just highlights the range and then hits the macro key.

Sub FormatCells()

Dim myRange As Range
Set myRange = ActiveCell.Selection (tried ActiveSheet.Selection and varoius others but to no avail!)

myRange.Interior.Color = vbBlue
'etc

End Sub

Wondered if anyone could provide some help

Thanks

Either of these will do.

Sub formatcells()
Dim myRange As Range
Set myRange = Selection
myRange.Interior.Color = vbBlue
End Sub

Sub formatcells22()
With Selection
..Interior.Color = vbBlue
End With
End Sub


Gord Dibben MS Excel MVP

On Sat, 15 Jul 2006 17:11:08 -0400, infojmac
<[email protected]> wrote:

>
>Hi,
>
>I'm sure this is so simple - but i cant get my head round it!
>
>All i want is the macro to run on the current selected range (just
>format some cells) but i dont want any message boxes the user just
>highlights the range and then hits the macro key.
>
>Sub FormatCells()
>
>Dim myRange As Range
>Set myRange = ActiveCell.Selection (tried ActiveSheet.Selection and
>varoius others but to no avail!)
>
>myRange.Interior.Color = vbBlue
>'etc
>
>End Sub
>
>Wondered if anyone could provide some help
>
>Thanks

try

Sub FormatCells()
Selection.Interior.ColorIndex = 5
End Sub



"infojmac" skrev:

>
> Hi,
>
> I'm sure this is so simple - but i cant get my head round it!
>
> All i want is the macro to run on the current selected range (just
> format some cells) but i dont want any message boxes the user just
> highlights the range and then hits the macro key.
>
> Sub FormatCells()
>
> Dim myRange As Range
> Set myRange = ActiveCell.Selection (tried ActiveSheet.Selection and
> varoius others but to no avail!)
>
> myRange.Interior.Color = vbBlue
> 'etc
>
> End Sub
>
> Wondered if anyone could provide some help
>
> Thanks
>
>
> --
> infojmac
> ------------------------------------------------------------------------
> infojmac's Profile: http://www.excelforum.com/member.php...o&userid=10787
> View this thread: http://www.excelforum.com/showthread...hreadid=561776
>
>

Sub test()

Dim rng As Range

Set rng = Selection

rng.Interior.ColorIndex = 3

End Sub

You can do it directly on Selection as well, without setting a range.

RBS

"infojmac" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> I'm sure this is so simple - but i cant get my head round it!
>
> All i want is the macro to run on the current selected range (just
> format some cells) but i dont want any message boxes the user just
> highlights the range and then hits the macro key.
>
> Sub FormatCells()
>
> Dim myRange As Range
> Set myRange = ActiveCell.Selection (tried ActiveSheet.Selection and
> varoius others but to no avail!)
>
> myRange.Interior.Color = vbBlue
> 'etc
>
> End Sub
>
> Wondered if anyone could provide some help
>
> Thanks
>
>
> --
> infojmac
> ------------------------------------------------------------------------
> infojmac's Profile:
> http://www.excelforum.com/member.php...o&userid=10787
> View this thread: http://www.excelforum.com/showthread...hreadid=561776
>

All working now.

Thanks everyone

Or simply

Sub formatcells()
Selection.Interior.Color = vbBlue
End Suib

Gord


On Sat, 15 Jul 2006 14:28:02 -0700, Gord Dibben <gorddibbATshawDOTca> wrote:

>Either of these will do.
>
>Sub formatcells()
>Dim myRange As Range
>Set myRange = Selection
> myRange.Sub formatcells22()
>End Sub
>
>Sub formatcells22()
>With Selection
>.Interior.Color = vbBlue
>End With
>End Sub
>
>
>Gord Dibben MS Excel MVP
>
>On Sat, 15 Jul 2006 17:11:08 -0400, infojmac
><[email protected]> wrote:
>
>>
>>Hi,
>>
>>I'm sure this is so simple - but i cant get my head round it!
>>
>>All i want is the macro to run on the current selected range (just
>>format some cells) but i dont want any message boxes the user just
>>highlights the range and then hits the macro key.
>>
>>Sub FormatCells()
>>
>>Dim myRange As Range
>>Set myRange = ActiveCell.Selection (tried ActiveSheet.Selection and
>>varoius others but to no avail!)
>>
>>myRange.Interior.Color = vbBlue
>>'etc
>>
>>End Sub
>>
>>Wondered if anyone could provide some help
>>
>>Thanks

Gord Dibben MS Excel MVP