sort data without changing pattern
 

I have a worksheet where every other row has a 'pattern' in it to help make
it easier to read across the spreadsheet.

The problem I'm running into is as I add new rows at the bottom of the
spreadsheet and then resort them into their correct place, the patterns go
with the sort and now the patterns have to be redone again the get them back
to every other row.

Any suggestions?

sort data without changing pattern
 

This will happen if your patterns are manually applied. It would be
better to remove that manual shading and to apply background colours
using conditional formatting, the condition being if the row number is
even (or odd) then colour the cell - the CF formula would be something
like:

=MOD(ROW(),2)=0

for even-numbered rows.

Hope this helps.

Pete

On Apr 6, 3:30*pm, Dan wrote:
I have a worksheet where every other row has a 'pattern' in it to help make
it easier to read across the spreadsheet.

The problem I'm running into is as I add new rows at the bottom of the
spreadsheet and then resort them into their correct place, the patterns go
with the sort and now the patterns have to be redone again the get them back
to every other row.

Any suggestions?

sort data without changing pattern
 

Rather than filling rows with a pattern, try a conditional format rule that
sets the fill to a color for every other row. For example, a rule in A1
that fills when =MOD(CELL("row",A1),2) is true, then copied to all rows,
fills every other row (in columns selected) with color. The color will
persist when the column is sorted.

g

"Dan" wrote:

I have a worksheet where every other row has a 'pattern' in it to help make
it easier to read across the spreadsheet.

The problem I'm running into is as I add new rows at the bottom of the
spreadsheet and then resort them into their correct place, the patterns go
with the sort and now the patterns have to be redone again the get them back
to every other row.

Any suggestions?

sort data without changing pattern
 

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

Used in Conditional Formatting as Formula.

First select a great whack of rows then enter the formula in activecell
only.


Gord Dibben MS Excel MVP


On Tue, 6 Apr 2010 07:30:03 -0700, Dan
wrote:

I have a worksheet where every other row has a 'pattern' in it to help make
it easier to read across the spreadsheet.

The problem I'm running into is as I add new rows at the bottom of the
spreadsheet and then resort them into their correct place, the patterns go
with the sort and now the patterns have to be redone again the get them back
to every other row.

Any suggestions?

sort data without changing pattern
 

Adding to all previous replies:
if you wanted to colour your sheet in bands of 3 (or any other number) of
rows you could use this formula in your conditional format:
=MOD(INT((ROW()-1)/$A$1),2)
where A1 contains the number of rows per band.

"Pete_UK" wrote:

This will happen if your patterns are manually applied. It would be
better to remove that manual shading and to apply background colours
using conditional formatting, the condition being if the row number is
even (or odd) then colour the cell - the CF formula would be something
like:

=MOD(ROW(),2)=0

for even-numbered rows.

Hope this helps.

Pete

On Apr 6, 3:30 pm, Dan wrote:
I have a worksheet where every other row has a 'pattern' in it to help make
it easier to read across the spreadsheet.

The problem I'm running into is as I add new rows at the bottom of the
spreadsheet and then resort them into their correct place, the patterns go
with the sort and now the patterns have to be redone again the get them back
to every other row.

Any suggestions?

.

sort data without changing pattern
 

Here's a general formula for setting a periodic conditional formatting:

=MOD(ROW()-offset,period)=0

will format every 'period' row, and move the pattern up or down by 'offset'.
For example, to highlight every fifth row with the first highlighting in row
4 the formula would read =MOD(ROW()+1,5)=0

g

"Gord Dibben" wrote:

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

Used in Conditional Formatting as Formula.

First select a great whack of rows then enter the formula in activecell
only.


Gord Dibben MS Excel MVP


On Tue, 6 Apr 2010 07:30:03 -0700, Dan
wrote:

I have a worksheet where every other row has a 'pattern' in it to help make
it easier to read across the spreadsheet.

The problem I'm running into is as I add new rows at the bottom of the
spreadsheet and then resort them into their correct place, the patterns go
with the sort and now the patterns have to be redone again the get them back
to every other row.

Any suggestions?

.