Excel Help

  SABRE 16:44 11 Aug 04

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 04

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

Or, get ASAPUtilities click here

  Big Trev 17:08 11 Aug 04

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 04

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 04

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 04

.Activate instead of .Select

would be quicker and save the flicker ;o))

just to be a pain.

  2020 23:06 11 Aug 04

may find this usefull some time

  SABRE 08:54 12 Aug 04

Will pursue your solutions.

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

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation techniques

How to mine Bitcoin on Mac