Highlight Sorted Alphabetical List
 

I thought it would be cool to sort my alphabetical list of names and then
have excel distinguish the a's from the b's from the c's, etc. Is there a way
to use the MOD function to tell excel to highlight the a's c's d's, etc. one
color and the b's d's e's, etc. another color all the way through the
alphabet?

If this is impossible, then is there a way to insert a break bar or
something between the lists of names at each new last name beginning letter?

My list is extensive and I audit by name (alphabetically) - To show the
break points between the names would be very helpful.

Thanks Y'all

Highlight Sorted Alphabetical List
 

Say your list is in column A. Use Conditional Formatting:
Formula Is
=ISEVEN(CODE(A1))

this will be False for a, c, e, ...
and True for b, d, f...


Just make sure your have the Analysis ToolPak installed
--
Gary''s Student - gsnu200716


"Teddy-B" wrote:

I thought it would be cool to sort my alphabetical list of names and then
have excel distinguish the a's from the b's from the c's, etc. Is there a way
to use the MOD function to tell excel to highlight the a's c's d's, etc. one
color and the b's d's e's, etc. another color all the way through the
alphabet?

If this is impossible, then is there a way to insert a break bar or
something between the lists of names at each new last name beginning letter?

My list is extensive and I audit by name (alphabetically) - To show the
break points between the names would be very helpful.

Thanks Y'all

Highlight Sorted Alphabetical List
 

Try this macro to color blocks of names (limited testing!):

Sub Color_Names()

Dim ws1 As Worksheet
Dim irow As Long
Dim Lastrow As Long
Dim col As Integer

Set ws1 = Worksheets("Sheet1") '<=== Change as required

col = 1 '<=== column for lastrow calculation

With ws1

Lastrow = .Cells(Rows.Count, col).End(xlUp).Row
ascii = 66 '<=== "B"
irow = 1 '<== Change to start row of data
xColor = 3 '<=== Red
Set rnga = Range("A:A")
Do
findvalue = Chr(ascii)
res = Application.Match(findvalue & "*", rnga, 0)
If Not IsError(res) Then
Cells(irow, 1).Resize(res - irow, 1).Interior.ColorIndex = xColor
xColor = xColor + 1
irow = res
End If
ascii = ascii + 1
Loop Until ascii 90

End With

End Sub

HTH

"Teddy-B" wrote:

I thought it would be cool to sort my alphabetical list of names and then
have excel distinguish the a's from the b's from the c's, etc. Is there a way
to use the MOD function to tell excel to highlight the a's c's d's, etc. one
color and the b's d's e's, etc. another color all the way through the
alphabet?

If this is impossible, then is there a way to insert a break bar or
something between the lists of names at each new last name beginning letter?

My list is extensive and I audit by name (alphabetically) - To show the
break points between the names would be very helpful.

Thanks Y'all

Highlight Sorted Alphabetical List
 

Perhaps a helper column in this situation....

With
A2:A500 containing a list of sorted names

Try something like this:
B2: =--OR(ROW()=1,IF(LEFT(A2,1)=LEFT(A1,1),B1,1-(B1=1)))

Then.....Select A2:A500

From the Excel main menu:
<format<conditional formatting
Condition_1
Formula is: =B2
Click the [format] button
.....select an appropriate contrasting cell shading pattern
.....Click [OK]
Click [OK]

That will create alternating bands that change color each time the first
letter of a cell does not match the first letter of the previous cell. Just
hide Col_B if its appearance is inelegant in your solution.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Teddy-B" wrote:

I thought it would be cool to sort my alphabetical list of names and then
have excel distinguish the a's from the b's from the c's, etc. Is there a way
to use the MOD function to tell excel to highlight the a's c's d's, etc. one
color and the b's d's e's, etc. another color all the way through the
alphabet?

If this is impossible, then is there a way to insert a break bar or
something between the lists of names at each new last name beginning letter?

My list is extensive and I audit by name (alphabetically) - To show the
break points between the names would be very helpful.

Thanks Y'all

Highlight Sorted Alphabetical List
 

Teddy

You have other responses for coloring.

If you want to sort and categorize with a break point letter try this macro.

Sub Alphabet_Sort()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Columns("A:A").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

FirstRow = 2
LastRow = Cells(Rows.Count, "a").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
If Left(Cells(iRow, "a").Value, 1) < _
Left(Cells(iRow - 1, "a").Value, 1) Then
Rows(iRow).Insert
With Cells(iRow, "a")
.Value = Left(Cells(iRow + 1, "a").Value, 1)
.Font.Bold = True
.HorizontalAlignment = xlCenterAcrossSelection
.Font.Underline = xlUnderlineStyleSingle
End With
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Wed, 18 Apr 2007 08:32:07 -0700, Teddy-B
wrote:

is there a way to insert a break bar or
something between the lists of names at each new last name beginning letter?

My list is extensive and I audit by name (alphabetically) - To show the
break points between the names would be very helpful.

Highlight Sorted Alphabetical List
 

Gord:
I liked your macro best of all. It is simple and effective.

"Gord Dibben" wrote:

Teddy

You have other responses for coloring.

If you want to sort and categorize with a break point letter try this macro.

Sub Alphabet_Sort()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Columns("A:A").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

FirstRow = 2
LastRow = Cells(Rows.Count, "a").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
If Left(Cells(iRow, "a").Value, 1) < _
Left(Cells(iRow - 1, "a").Value, 1) Then
Rows(iRow).Insert
With Cells(iRow, "a")
.Value = Left(Cells(iRow + 1, "a").Value, 1)
.Font.Bold = True
.HorizontalAlignment = xlCenterAcrossSelection
.Font.Underline = xlUnderlineStyleSingle
End With
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Wed, 18 Apr 2007 08:32:07 -0700, Teddy-B
wrote:

is there a way to insert a break bar or
something between the lists of names at each new last name beginning letter?

My list is extensive and I audit by name (alphabetically) - To show the
break points between the names would be very helpful.

Highlight Sorted Alphabetical List
 

Thanks for the feedback.

Gord

On Wed, 18 Apr 2007 11:34:02 -0700, Teddy-B
wrote:

Gord:
I liked your macro best of all. It is simple and effective.

"Gord Dibben" wrote:

Teddy

You have other responses for coloring.

If you want to sort and categorize with a break point letter try this macro.

Sub Alphabet_Sort()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Columns("A:A").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

FirstRow = 2
LastRow = Cells(Rows.Count, "a").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
If Left(Cells(iRow, "a").Value, 1) < _
Left(Cells(iRow - 1, "a").Value, 1) Then
Rows(iRow).Insert
With Cells(iRow, "a")
.Value = Left(Cells(iRow + 1, "a").Value, 1)
.Font.Bold = True
.HorizontalAlignment = xlCenterAcrossSelection
.Font.Underline = xlUnderlineStyleSingle
End With
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Wed, 18 Apr 2007 08:32:07 -0700, Teddy-B
wrote:

is there a way to insert a break bar or
something between the lists of names at each new last name beginning letter?

My list is extensive and I audit by name (alphabetically) - To show the
break points between the names would be very helpful.