Highlight Sorted Alphabetical List

T

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
 
G

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
 
T

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
 
R

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
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top