Loop macro in selected range

[Sjaakve]

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

 

[Ron de Bruin]

Use it like this

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

 

[excel-ant]

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

 

[Mike H]

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

 

[Ron de Bruin]

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

 

[Sjaakve]

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

 

[Mike H]

Then us

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

Mike

 

[Dave Peterson]

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

 

[Sjaakve]

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