Shading every other row

[Guest]

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.

 

[Guest]

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

 

[Bob Phillips]

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

--
---
HTH

Bob

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

 

[Guest]

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.

 

[dustin]

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

 

[Roger Govier]

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.

 

[Gord Dibben]

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

 

[Roger Govier]

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

 

[Gord Dibben]

Thanks Roger.

I'll add it to my list.


Gord