Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop thru range and determine if filename exists in selected folder | Excel Programming | |||
trying to delete selected range with macro | Excel Programming | |||
Macro run if and cell in range is selected in VBA | Excel Programming | |||
Macro run if and cell in range is selected in VBA | Excel Programming | |||
TO TOM OR KEN: macro to calculate selected range | Excel Programming |