Loop macro in selected range
 

Hi,

I am new to VBA. I dug up a macro on the web somewhere. It does what
is has to do, but only for the active cell. When I select multiple
cells the macro only works on one cell. I would like it to loop, and
do all the cells.

I tried recording a macro for a selection, but only got this:

Range("A1:A4,C7,D9,A11:G11").Select
Range("A11").Activate
Application.Run "Macro1"

and that several times


This is what I would like:

I've selected A1:A4,C7,D9,A11:H11

The excisting macro is named Macro1 and gives the cell a red color.

If I run the macro now only A11 turns red.

I would like a macro that repeats Macro1 for the active selection.

Can anybody tell me how this is done?

Thanx,

Sjaak

Loop macro in selected range
 

Use it like this

Range("A1:A4,C7,D9,A11:G11").Interior.ColorIndex = 3


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Sjaakve" wrote in message ...
Hi,

I am new to VBA. I dug up a macro on the web somewhere. It does what
is has to do, but only for the active cell. When I select multiple
cells the macro only works on one cell. I would like it to loop, and
do all the cells.

I tried recording a macro for a selection, but only got this:

Range("A1:A4,C7,D9,A11:G11").Select
Range("A11").Activate
Application.Run "Macro1"

and that several times


This is what I would like:

I've selected A1:A4,C7,D9,A11:H11

The excisting macro is named Macro1 and gives the cell a red color.

If I run the macro now only A11 turns red.

I would like a macro that repeats Macro1 for the active selection.

Can anybody tell me how this is done?

Thanx,

Sjaak

Loop macro in selected range
 

On 12 Mar, 20:14, Sjaakve wrote:
Hi,

I am new to VBA. I dug up a macro on the web somewhere. It does what
is has to do, but only for the active cell. When I select multiple
cells the macro only works on one cell. I would like it to loop, and
do all the cells.

I tried recording a macro for a selection, but only got this:

* * Range("A1:A4,C7,D9,A11:G11").Select
* * Range("A11").Activate
* * Application.Run "Macro1"

and that several times

This is what I would like:

I've selected A1:A4,C7,D9,A11:H11

The excisting macro is named Macro1 and gives the cell a red color.

If I run the macro now only A11 turns red.

I would like a macro that repeats Macro1 for the active selection.

Can anybody tell me how this is done?

Thanx,

Sjaak

Yep,

sub SelectionLoop

dim cl as selection

for each cl in Selection
-- insert code here changing cell to cl<----
next

end sub

Ant
http://www.excel-ant.co.uk

Loop macro in selected range
 

Hi,

There are better ways than this but without seeing macro1; i wrote my own
and guessed what yours does, you could try this.


Sub standard()
For Each c In Range("A1:A4,C7,D9,A11:G11")
c.Select
Application.Run "Macro1"
Next
End Sub

Sub Macro1()
ActiveCell.Interior.ColorIndex = 3
End Sub

Mike
"Sjaakve" wrote:

Hi,

I am new to VBA. I dug up a macro on the web somewhere. It does what
is has to do, but only for the active cell. When I select multiple
cells the macro only works on one cell. I would like it to loop, and
do all the cells.

I tried recording a macro for a selection, but only got this:

Range("A1:A4,C7,D9,A11:G11").Select
Range("A11").Activate
Application.Run "Macro1"

and that several times


This is what I would like:

I've selected A1:A4,C7,D9,A11:H11

The excisting macro is named Macro1 and gives the cell a red color.

If I run the macro now only A11 turns red.

I would like a macro that repeats Macro1 for the active selection.

Can anybody tell me how this is done?

Thanx,

Sjaak

Loop macro in selected range
 

If you want to loop use this

Sub Test()
Dim smallrng As Range

For Each smallrng In Range("A1:A4,C7,D9,A11:G11").Areas
With smallrng
.Interior.ColorIndex = 3
End With
Next smallrng

End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Ron de Bruin" wrote in message ...
Use it like this

Range("A1:A4,C7,D9,A11:G11").Interior.ColorIndex = 3


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Sjaakve" wrote in message ...
Hi,

I am new to VBA. I dug up a macro on the web somewhere. It does what
is has to do, but only for the active cell. When I select multiple
cells the macro only works on one cell. I would like it to loop, and
do all the cells.

I tried recording a macro for a selection, but only got this:

Range("A1:A4,C7,D9,A11:G11").Select
Range("A11").Activate
Application.Run "Macro1"

and that several times


This is what I would like:

I've selected A1:A4,C7,D9,A11:H11

The excisting macro is named Macro1 and gives the cell a red color.

If I run the macro now only A11 turns red.

I would like a macro that repeats Macro1 for the active selection.

Can anybody tell me how this is done?

Thanx,

Sjaak

Loop macro in selected range
 

That was fast.

That helps a lot. Only, the range is not always the same. Next time i
might want to select B12,G3,D2:F5 and run the macro for each cell.

Macro1() does not turn the cell red, it was just an example. I didn't
want to complicate things any further.

Is it possible the range is variable?

thanx

Sjaak

Loop macro in selected range
 

Then us

Sub standard()
For Each c In selection
c.Select
Application.Run "Macro1"
Next
End Sub

Mike


"Sjaakve" wrote:

That was fast.

That helps a lot. Only, the range is not always the same. Next time i
might want to select B12,G3,D2:F5 and run the macro for each cell.

Macro1() does not turn the cell red, it was just an example. I didn't
want to complicate things any further.

Is it possible the range is variable?

thanx

Sjaak

Loop macro in selected range
 

But if Macro1 is in the same workbook project as standard, then you could just
call it:

Sub standard()
For Each c In selection
c.Select
Call Macro1
Next c
End Sub



Mike H wrote:

Then us

Sub standard()
For Each c In selection
c.Select
Application.Run "Macro1"
Next
End Sub

Mike

"Sjaakve" wrote:

That was fast.

That helps a lot. Only, the range is not always the same. Next time i
might want to select B12,G3,D2:F5 and run the macro for each cell.

Macro1() does not turn the cell red, it was just an example. I didn't
want to complicate things any further.

Is it possible the range is variable?

thanx

Sjaak


--

Dave Peterson

Loop macro in selected range
 

Thank you guys!

It all works like a charm.

Dave hit the jackpot.

All imaginable selections work, each cell get the required work done.

Sjaak