Shading every other row

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alan - 21 Mar 2007 00:14 GMT

Any suggestions on how to quickly select every other row OR fill every other
row? With large lists I like to color every other row light gray for refrence
purposes.

Reply to this Message

Elkar - 21 Mar 2007 00:30 GMT

You could use Conditional Formatting.

Select your range of cells to shade
From the Format Menu, choose "Conditional Formatting..."
Change "Cell Value Is" to "Formula Is"
Enter the formula: =MOD(ROW(),2)=0
Select your format (gray background)
Click OK

HTH,
Elkar

> Any suggestions on how to quickly select every other row OR fill every other
> row? With large lists I like to color every other row light gray for refrence
> purposes.

Reply to this Message

Alan - 21 Mar 2007 22:19 GMT

Thank you Elkar! This solutions brings up another question. Is there anyway
to use this solution to make every other column in a selected row switch
positions. (ie. what was in A1 moves to A2 and what was in A2 moves to A1, A3
switches with A4, A5 with A6... all the way down). If so it would solve a
flash card making problem in which (through a Word mailmerge) the fronts of
the cards (column A = words) do not have the correct back of card (column B =
word definition). I think the problem would be solved by inverting every
other row in excel then doing the mail merge on word.

> You could use Conditional Formatting.
>
[quoted text clipped - 11 lines]
> > row? With large lists I like to color every other row light gray for refrence
> > purposes.

Reply to this Message

Bob Phillips - 21 Mar 2007 00:42 GMT

See http://www.xldynamic.com/source/xld.CF.html#rows

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Any suggestions on how to quickly select every other row OR fill every
> other
> row? With large lists I like to color every other row light gray for
> refrence
> purposes.

Reply to this Message

dustin - 17 May 2008 09:10 GMT

Alan, did you ever figure out how to do this becuase I am running into the
same problem trying to make some notecards for my medical school board exams.
If you figured out how to do this I woul greatly appreciate it because I
have all my data in two column format, but like you said the cards don't line
up right when you try and print it two sided.

thanks,
dustin

> Any suggestions on how to quickly select every other row OR fill every other
> row? With large lists I like to color every other row light gray for refrence
> purposes.

Reply to this Message

Roger Govier - 17 May 2008 09:47 GMT

Hi Dustin

Select the range of rows / columns that you wish to color.
Format>Conditional Formatting>use dropdown to select Formula is>
=MOD(ROW(),2)=0 >Format>Choose whatever Fill color you want.

Signature

Regards
Roger Govier

> Alan, did you ever figure out how to do this becuase I am running into the
> same problem trying to make some notecards for my medical school board
[quoted text clipped - 12 lines]
>> refrence
>> purposes.

Reply to this Message

Gord Dibben - 17 May 2008 16:51 GMT

Select a gaggle of rows then..........

Format>Conditional Formatting>Formula is:

=MOD(SUBTOTAL(3,$A1:$A$2),2)=0

Pick a gray from the Format>Patterns

The difference between Roger's formula and mine is that mine will hold the
banding through filtering.

Otherwise............no difference.

Gord Dibben MS Excel MVP

>Alan, did you ever figure out how to do this becuase I am running into the
>same problem trying to make some notecards for my medical school board exams.
[quoted text clipped - 8 lines]
>> row? With large lists I like to color every other row light gray for refrence
>> purposes.

Reply to this Message

Roger Govier - 17 May 2008 19:01 GMT

Excellent idea Gord.

May be sensible to AND another condition to prevent any blank rows below the
data from picking up the formatting
=AND(MOD(SUBTOTAL(3,$A1:$A$2),2)=0,COUNTA(ROW())>0)
Subtotal(3,rng) will give strange results if there are numbers in in say the
first 9 of 20 rows formatted, and blank cells in the remaining 20

Signature

Regards
Roger Govier

> Select a gaggle of rows then..........
>
[quoted text clipped - 32 lines]
>>> refrence
>>> purposes.

Reply to this Message

Gord Dibben - 17 May 2008 21:31 GMT

Thanks Roger.

I'll add it to my list.

Gord

>Excellent idea Gord.
>
[quoted text clipped - 3 lines]
>Subtotal(3,rng) will give strange results if there are numbers in in say the
>first 9 of 20 rows formatted, and blank cells in the remaining 20

Reply to this Message