Excel Help

  SABRE 16:44 11 Aug 2004

Is there any way to format the odd rows of a spreedsheet in one colour and the even rows in another?

  VoG II 17:07 11 Aug 2004

Yes. There's the "hard" way - write a macro.

Or, get ASAPUtilities click here

  Big Trev 17:08 11 Aug 2004

One way to do it is hold down the Control Button and Highlight the odd numbers and thereby the whole row and then click the colour of filler you want. It will colour all odd rows that colour. A bit clunky, but it works.

  cga 17:13 11 Aug 2004

Only valid before you build the sheet.

You could format the first two rows and then use the power of binary to do copies.

i.e Copy row 1-2 into 3-4, copy 1-4 into 5-8, etc.

btw I expect you know this but when you copy you only need to select the top left cell of the target.

  cherria 17:26 11 Aug 2004

Format ->Autoformat

scroll down until you see the one with alternate shading and select this.

To complete VoG™ suggestion for the hard way the following Macro will colour the alternate rows in the range A1 to M30.


Sub AlternateColorRows()


With Selection.Interior

.ColorIndex = 36 'Pale Yellow

.Pattern = xlSolid

End With


With Selection.Interior

.ColorIndex = 35 'Pale Green

.Pattern = xlSolid

End With


Selection.AutoFill Destination:=Rows("1:30"), Type:=xlFillDefault


End Sub


If you want a complete row then change the 'Range("A1:M1")' to read Rows("1:1") and similar for row 2. Again the destination Rows can be changed to suit your needs.

As cga posted you can do it manually by selecting the first row and put in the colour, do the same with the second row with its colour, but now select both rows 1 & 2 and use the fill handle to the bottom left of the 2nd row to fill down to the number of rows you require.

The same caveat applies that the formatting must be done on a fresh worksheet


.Pattern = xlSolid End With

should read

.Pattern = xlSolid

End With

  VoG II 22:15 11 Aug 2004

.Activate instead of .Select

would be quicker and save the flicker ;o))

just to be a pain.

  2020 23:06 11 Aug 2004

may find this usefull some time

  SABRE 08:54 12 Aug 2004

Will pursue your solutions.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

What went wrong at the Designs of the Year 2017

iPhone X news: Release date, price, new features & specs

Comment regarder des séries et talk-shows américains en France ?