Alphabetically list of last names: BOLD, not bold

[Lerner]

Actually none of them had work.
A B C

1 NAMES

2 ARIES, JOHN

3 ACTUAL, JANE

4 CONSECUTIVE, MIKE

5 EXCEL, MICROSOFT

6 EVEREST, TRISIA

7 HINGS, PAULA

Basically the thing is get the first one ( last name with A) BOLD and then
alternating with the list (not bold, bold, not bold, bold) regardless of the
missing secuency as in this case it will be A>BOLD, C>not bold, E> bold,
H> not bold.
I'd rather use conditional formatting than MACROS.
Thanks.

 

[Gary''s Student]

Do you want all of the items beginning with "A" to be bold or only the first
item?
Do you want only the lastname to be bold or the fullname?

 

[Lerner]

All items beggining with A BOLD
Last name and Fist name BOLD (full name)as they are in the same cell.
THANKS Gary.

 

[Gary''s Student]

This assumes that the names are stored in column A starting with A1. Enter
run run the following macro:

Sub boldAlternator()
Dim n As Long, FlipFlop As Boolean
n = Cells(Rows.Count, 1).End(xlUp).Row
Cells(1, 1).Font.Bold = True
FlipFlop = True
For i = 2 To n
If Left(Cells(i, 1).Value, 1) = Left(Cells(i - 1, 1).Value, 1) Then
Else
FlipFlop = Not FlipFlop
End If
Cells(i, 1).Font.Bold = FlipFlop
Next
End Sub

Suppose we start with data like:

Adams, Raymond
Allen, Timothy
Anderson, Christopher
Baker, Gregory
Brown, William
Campbell, Henry
Carter, Dennis
Clark, Ronald
Collins, Roger
Davis, David
Edwards, Ryan
Evans, Arthur
Garcia, Steven
Gonzalez, Joshua
Green, Andrew
Hall, Gary
Harris, Donald
Hernandez, Larry
Hill, Eric
Jackson, Paul
Johnson, John
Jones, Michael
King, Jeffrey
Lee, Jason
Lewis, Kevin
Lopez, Scott
Martin, George
Martinez, Edward
Miller, Richard
Mitchell, Walter
Moore, Joseph
Nelson, Jerry
Parker, Carl
Perez, Patrick
Phillips, Douglas
Roberts, Peter
Robinson, Brian
Rodriguez, Anthony
Scott, Stephen
Smith, James
Taylor, Thomas
Thomas, Daniel
Thompson, Kenneth
Turner, Harold
Walker, Matthew
White, Mark
Williams, Robert
Wilson, Charles
Wright, Frank
Young, Jose


The following name will be Bold, the other not:

Adams, Raymond
Allen, Timothy
Anderson, Christopher
Campbell, Henry
Carter, Dennis
Clark, Ronald
Collins, Roger
Edwards, Ryan
Evans, Arthur
Hall, Gary
Harris, Donald
Hernandez, Larry
Hill, Eric
King, Jeffrey
Martin, George
Martinez, Edward
Miller, Richard
Mitchell, Walter
Moore, Joseph
Parker, Carl
Perez, Patrick
Phillips, Douglas
Scott, Stephen
Smith, James
Walker, Matthew
White, Mark
Williams, Robert
Wilson, Charles
Wright, Frank

 

[Lerner]

Thanks Gary, for going with the extra effort and detailed explanation
in order to help me, I really appreciate your time.
Not meaning to be a pain, but this means Conditional Formatting
won't be able to do it ?
Thank you again.

 

[Gary''s Student]

Conditional formatting might be used if we can figure out how to detect if
the row above the row being formatted is bold or not.

If would be easy if all the "A" are bold, all the "B" are not, all the "C"
are bold.

In your case, if there are only "A" and "C", you want the "C" to be not bold.


Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select the macro
3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and we can adjust the code to work with any column and any starting cell!

 

[francis]

Lerner

Conditional Formatting can do it but indirectly.
I have provided you a solution in your old post without a macro
as you have mentioned that you don't want to use macro if possible
otherwise the macro provided by Sheeloo may have done the job.


--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis

 

[Lerner]

Thanks, Francis, however using the 'dummy column' did not give me the results
expected as it just BOLDED the first item in the row(unless something wrong
was done by me).
I have some macros in some workbooks, but the especific problem is that all
of those workbooks
with macros take an eternity to save the workbook everytime.

 

[Lerner]

Thanks a lot for the information and website Gary.

 

[francis]

Lerner

I have tested the result using the names in this thread and the result
is the same as using the macro provided by Gary

Did you place the 1st formula in B2 and copy down? if you have place this
formula in some other column, you need to adjust the formula to that column.
and when applying the CF, did you select all the cells, ie from A1 to A100
or wherever it end, before you place the 2nd formula in CF.


..
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis

 

[Sheeloo]

Hello Lerner,

I had given you a similar solution (copied below) to your yesterday's post
and provided clarification requested by you but you did not respond to that...

Pl. avoid doing this in future...

Code;
Sub highlight()
Dim lastRow, lastCol As Double
Dim pName As String
Dim flg As Boolean

With ActiveSheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
lastCol = .Cells(.Columns.Count, "A").End(xlToLeft).Column
End With

pName = ""
flg = True

For i = 2 To lastRow

If pName <> Left(Cells(i, 1).Value, 1) Then
pName = Left(Cells(i, 1).Value, 1)
If flg = True Then
flg = False
Else
flg = True
End If

End If

If flg = True Then
'format

With Cells(i, 1).Characters(Start:=1, Length:=1).Font
..FontStyle = "Bold"
End With
With Cells(i, 1).Characters(Start:=2, Length:=255).Font
..FontStyle = "Regualar"
End With

End If

Next

End Sub

Your question which I had answered but ...

 

[Lerner]

It did not work because a simple detail:
You wrote in FORMULA IS: =B1=O (Incorrect)
I started changing in around and the correct one is:
FORMULA IS: =B2=O (right one)
Probably that's what you did when it worked but by mistake or typo you
posted =B1=0 (Incorrect).
However the human error, VERY CLEAVER interesting formula,
got it handed to you, well deserved congratulations.
However(never ends) if you read my posts carefully
The BOLD should start the secuency and I get NOT BOLD first,
I'm sure there is an adjustment I can find as a homework as
I don't want to bother anymore.
Thanks again francis.

 

[Lerner]

I will. Thanks Sheeloo.

Hello Lerner,

I had given you a similar solution (copied below) to your yesterday's post
and provided clarification requested by you but you did not respond to that...

Pl. avoid doing this in future...

Code;
Sub highlight()
Dim lastRow, lastCol As Double
Dim pName As String
Dim flg As Boolean

With ActiveSheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
lastCol = .Cells(.Columns.Count, "A").End(xlToLeft).Column
End With

pName = ""
flg = True

For i = 2 To lastRow

If pName <> Left(Cells(i, 1).Value, 1) Then
pName = Left(Cells(i, 1).Value, 1)
If flg = True Then
flg = False
Else
flg = True
End If

End If

If flg = True Then
'format

With Cells(i, 1).Characters(Start:=1, Length:=1).Font
.FontStyle = "Bold"
End With
With Cells(i, 1).Characters(Start:=2, Length:=255).Font
.FontStyle = "Regualar"
End With

End If

Next

End Sub

Your question which I had answered but ...

 

[Sheeloo]

No hard feelings..

I am curious though... did you try out my solution? If yes, then did it work?

Code provided by Gary's student is more elegant...

 

[Lerner]

Honestly I have not try yours yet, but I'm sure will.
Thanks.

 

[Shane Devenshire]

Hi,

the fact is that many of the solutions worked from yeasterdays post. You
should always try them before you conclude they won't work. And as Sheeloo
has pointed out, most correctly, you should keep your posts together.

 

[Lerner]

That's right all of them worked but yours Shane.
Thanks to all of you for your participation.