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 (jus
format some cells) but i dont want any message boxes the user jus
highlights the range and then hits the macro key.

Sub FormatCells()

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

myRange.Interior.Color = vbBlue
'etc

End Sub

Wondered if anyone could provide some help

Thank

--
infojma
-----------------------------------------------------------------------
infojmac's Profile: http://www.excelforum.com/member.php...fo&userid=1078
View this thread: http://www.excelforum.com/showthread.php?threadid=56177

Current Selected Range
 

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

Current Selected Range
 

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

Current Selected Range
 

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" wrote in
message ...

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

Current Selected Range
 

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

Current Selected Range
 

All working now.

Thanks everyon

--
infojma
-----------------------------------------------------------------------
infojmac's Profile: http://www.excelforum.com/member.php...fo&userid=1078
View this thread: http://www.excelforum.com/showthread.php?threadid=56177