Highlight Sorted Alphabetical List

[Teddy-B]

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

 

[Gary''s Student]

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

 

[Toppers]

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

 

[Ron Coderre]

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

 

[Gord Dibben]

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

 

[Teddy-B]

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

 

[Gord Dibben]

Thanks for the feedback.

Gord

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