# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  Current Selected Range

## infojmac

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

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
<infojmac.2b09f6_1152998109.0779@excelforum-nospam.com> 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

----------


## excelent

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
>
>

----------


## RB Smissaert

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" <infojmac.2b09f6_1152998109.0779@excelforum-nospam.com> wrote in
message news:infojmac.2b09f6_1152998109.0779@excelforum-nospam.com...
>
> 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
>

----------


## infojmac

All working now. 

Thanks everyone

----------


## Gord Dibben

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
><infojmac.2b09f6_1152998109.0779@excelforum-nospam.com> 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

----------

