Excel macro to operate on selected range 2

[saw]

Hi everyone,
I would like to be able to select a range in an excel spreadsheet, then run a macro that would change the background color to light gray on every other row (of the selected range). How can I do this in an excel macro?

thanks for any help,
Scott

 

[PHV]

What have you so far ?
Have you tried to play with the macro recorder ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[saw]

Yep, tried the macro recorder and actually made a macro that does the alternate shade idea, but it only does in on a set range. I'd like to have the macro read the range I manually select each time, then apply the light-grey background to every other row.

thanks, Scott

 

[TonyJollans]

Hi saw,

Not the easiest thing to record, I guess. Try this ..

[blue]For Each Row In Selection.Rows
    Row.Interior.ColorIndex = (Row.Row Mod 2) * 15
Next[/blue]

This will color odd numbered rows colorindex 15, which on my machine at the moment is a kind of grey.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[saw]

Oh yes!! Thank you Tony, that is exactly what I wanted to do and its the right shade of grey too! That is so slick. Nice, simple and just right.
Thanks again Tony!

Scott

 

[msc0tt]

Sorry to tag onto a completed thread, but this topic is so close to what I need....

I need my macro to cycle through all the currently selected cells. Is there something like:

For Each Cell in Selection.Cells
'process contents of Cell
Next

It is possible that my selection will not be a rectangle, so I can't just double-loop it, row/col style.
-thanks

 

[TonyJollans]

Hi msc0tt,

It is exactly like you suggest - no need for extra loops or anything else, just ..

[blue]For Each C in Selection
    [green]' Process Cell "C"[/green]
Next[/blue]

This will process all cells in any disjoint selection.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[msc0tt]

Thanks Tony. This does indeed do exactly what I need.

btw: do you know a good reference website (besides MS) for the Excel Object Model? I learned the trick of using the macro recorder some time ago, but sometimes this still isn't enough.

Thanks again,
Mike

 

[TonyJollans]

Hi Mike,

There are several good Excel websites, but off the top of my head I'm afraid I don't know which of them address the object model the best - I tend to use Help or the Object Browser (F1 or F2 in the VBE) - and a lot of trial and error!

Have an excellent Christmas

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.