Macro to colour alternating rows

[Rudey]

Hi,

I was wondering, what would the macro be to colour alternating rows.

So I could select the range of rows, hit the macro - and it would colour them for me?

I need it to do alternating green / lightgreen. For one, but I need to do other colours afterwards as well. So what would the code be to do this?

Thanks
-Rudey

[Paul]

Hi Rudey,

In Excel 2007 you have a few options without resorting to a macro.

1. Use a Table style. Select your table of data, then in the Home ribbon click 'Format as Table' and either select a table style from the ones shown, or create your own by selecting 'New Table Style'

2. Use Conditional Formatting (in 2007 you can have more than 3, so you can have 4, 5, 6 or more alternating colors) using the MOD function. For example:

To highlight rows 1, 4, 7, 10, etc. with green, select your table of data and click Conditional Formatting on the Home ribbon. Select 'New Rule'. Select 'Use a formula to determine which cells to format', then in the formula textbox provided below it, type:
=MOD(ROW(),3)=1

Then click the Format button and select a Green fill color.

To highlight rows 2, 5, 8, 11, etc., add another New Rule, but the formula this time should be:
=MOD(ROW(),3)=2

For the third set of rows (3, 6, 9, 12, etc.) use:
=MOD(ROW(),3)=0

If you still need a macro, let us know.

[Rudey]

Hey,

Yeah I tried that out. But I'm using conditional formatting in my spreadsheet. And I need to do this in alot of places. So it just creates way ot many rules for me to sort though.

I was also wanting to learn alittle about macros in order to do this.

Thanks!

[Paul]

The macro *could* look something like this (for three alternating colors):

Please Login or Register  to view this content.

To find the right code for the color you want, you can search online for a list or palette of Excel 2007 colors, or simply record a macro while you set a cell's fill color to the color you want and review the code generated.

Notice the two different methods of coloring used - one for "standard" colors and other for "theme" colors. When you record the macro for the color you want, you'll see which type you need to use.

Hopefully that helps.

[Rudey]

Hi thanks for that. I get an error at:

Please Login or Register  to view this content.

When I run it though

[Paul]

Where did you put the code? It should be in one of the Worksheet's code windows, not a module. If necessary, add a Sheet name before UsedRange, e.g.

Please Login or Register  to view this content.

[Rudey]

Sorry I'm confused. What's meant by a code window instead of a module?

[Rudey]

Ah awesome. Made it run. BUt how would I make it only apply this formatting ONLY to the area I select. So if I select A1:A10 it would only apply that formatting to those cells.

Also, thanks for teaching me about Cases I was looking for a good example of when to use them!

[Rudey]

Would anyone know the code to limit the colouring of the code to the area I select only?

Edit: I came up with some code, but it has an error that is: Next without For. Could someone please help me with it, because I have never programmed VBA before.

Please Login or Register  to view this content.

Thanks

[Paul]

Hi Rudey, try this updated code (changes in red):

Please Login or Register  to view this content.

Hope that helps.

[Rudey]

Works! Thanks heaps!