sorting without changing formats
 

I am creating a spreadsheet similar to a school attendance record so I have a
white row and then a gray row. When I enter the children's names and run a
sort to put them in alphabetical order, it also sorts the cell format. Is
there anyway to make the form permanent and just the data sortable?

sorting without changing formats
 

Select the cells that you want to format with an alternating gray and white
row.

FOrmat - COnditional Formatting

Set your condition to Formula is =mod(row(),2)=0 and set the shading you
want.

"LisaD" wrote:

I am creating a spreadsheet similar to a school attendance record so I have a
white row and then a gray row. When I enter the children's names and run a
sort to put them in alphabetical order, it also sorts the cell format. Is
there anyway to make the form permanent and just the data sortable?

sorting without changing formats
 

Here is a program I wrote a while ago to shade a spreadsheet Red, white, and
blue

Const EndColumn As String = "F"
Const EndRow As String = "94"
Const StartColumn As String = "A"
Const StartRow As String = "3"

Const SHADERED As Integer = 3
Const SHADEBLUE As Integer = 33

Dim RowOffsetCount As Integer

RowOffsetCount = 0
Do While Cells(Val(StartRow) + RowOffsetCount, StartColumn).Value < ""

Range(StartColumn + StartRow + ":" + EndColumn + StartRow). _
Offset(rowOffset:=RowOffsetCount, _
columnOffset:=0).Select

With Selection.Interior
Select Case (RowOffsetCount Mod 3)

Case 0
.ColorIndex = SHADERED
.Pattern = xlSolid
Case 1
.ColorIndex = xlNone
Case 2
.ColorIndex = SHADEBLUE
.Pattern = xlSolid
End Select

End With

RowOffsetCount = RowOffsetCount + 1
Loop

End Sub

"LisaD" wrote:

I am creating a spreadsheet similar to a school attendance record so I have a
white row and then a gray row. When I enter the children's names and run a
sort to put them in alphabetical order, it also sorts the cell format. Is
there anyway to make the form permanent and just the data sortable?

sorting without changing formats
 

Use conditional formatting with a formula.

Select the cells, or even entire rows that you want to be alternately
shaded. Then use Format | Conditional Format
and select the "Formula Is" (not the default "Value is") and enter this
formula:
=MOD(ROW(), 2)=0
then set the format for the Even numbered rows. This formula will leave odd
numbered rows unchanged, but will 'shade' the even numbered rows.

If you want to go the other way, shading the odd numbered rows then the
formula would be:
=MOD(ROW(), 2)=1


"LisaD" wrote:

I am creating a spreadsheet similar to a school attendance record so I have a
white row and then a gray row. When I enter the children's names and run a
sort to put them in alphabetical order, it also sorts the cell format. Is
there anyway to make the form permanent and just the data sortable?

sorting without changing formats
 

3 options..

enter the names on a clean unshaded sheet
do the sort
copy the names and
go to a sheet with the shaded lines
and edit... paste special... values

or
have a list of pupils on a shaded lines sheet in alphabetical order
just mark cells next to the names

or
use a macro to do the sort and re- shade the lines after the sort

Sub ShadeEveryOtherRow()
Dim Counter As Integer

Sheets("sheet4").Select
Range("A1:M40").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Range("A1").Select

For Counter = 1 To 40
'If the row is an odd number (within the selection)...
If Counter Mod 2 = 1 Then
'Set the pattern to xlGray16.
Rows(Counter).Interior.Pattern = xlGray16

Else
Rows(Counter).Interior.Pattern = nil
End If
Next

End Sub

You will have to adjust the ranges to suit.
--
Greetings from New Zealand

"LisaD" wrote in message
...
I am creating a spreadsheet similar to a school attendance record so I have
a
white row and then a gray row. When I enter the children's names and run a
sort to put them in alphabetical order, it also sorts the cell format. Is
there anyway to make the form permanent and just the data sortable?