sort data without changing pattern

[Dan]

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?

 

[Pete_UK]

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

 

[GeoBrooks]

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

 

[Gord Dibben]

=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

 

[Michael_R]

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.

 

[GeoBrooks]

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