sort columns, but not shading

[on-a-mission]

i have a worksheet with data in column A, every row is either grey o
white shading. i always want to keep the shading alterante grey/white

a1: 7 (white shading)
a2: 4 (grey shading)
a3: 3 (white shading)
a4: 5 (grey shading)
a5: 1 (white shading)
a6: 2 (grey shading)

when i sort assending it mucks up the shading becuase it moves the dat
as well as the shaded cell.
is there any way of keeping the cells locked and just sort the data??

 

[David McRitchie]

What you want to use is Conditional Formatting.

For Color Banding the best place to read about it is at Chip Pearson's
basically to band even rows (leave header row alone) =MOD(ROW(),2)=0
but you will see formulas for any number of rows banded,
and which row to start banding color on at
Color Banding With Conditional Formatting
http://www.cpearson.com/excel/banding.htm

If you were using Filtering there is a different formula you can use, but
you have to have every cell in column A used of those rows that are showing
so it is not as general purpose but =MOD(SUBTOTAL(3,$A$1:$A1),2)
would band alternate rows even with filtering. . You must have activecell on A1
when entering formula.
Conditional Formatting: Color Banding
http://www.mvps.org/dmcritchie/excel/condfmt.htm#colorband

 

[Pete_UK]

Highlight all the cells and set the shading to No Fill, then with the
cells still highlighted click Format | Conditional Formatting, then
select "Formula is" in the first drop down, then enter:

=MOD(ROW(),2)=0

in the next panel. Then click on Format and Patterns (tab) and select
the shade of grey you require.

This will give you alternate white and grey shading which is unaffected
by sorting the data.

Hope this helps.

Pete

 

[gls858]

i have a worksheet with data in column A, every row is either grey or
white shading. i always want to keep the shading alterante grey/white

a1: 7 (white shading)
a2: 4 (grey shading)
a3: 3 (white shading)
a4: 5 (grey shading)
a5: 1 (white shading)
a6: 2 (grey shading)

when i sort assending it mucks up the shading becuase it moves the data
as well as the shaded cell.
is there any way of keeping the cells locked and just sort the data???

This doesn't really lock the shading but it makes it really easy to redo.
Look at a freebie program called ASAP Utilities

http://www.asap-utilities.com/

There's a selection that lets you color every nth row or column. Lots of
other useful tools included in the package.

gls858