Alphabetically list of names BOLD and NOT bold

[Lerner]

I have a list of alphabetically last names, first names sorted that I' like to
conditionally format by BOLDED or NOT bolded.
All last names startin with letter A = BOLD
Going down then B = not bold
Then last name starting with letter C could be missing or not:
If C last names present then C=BOLD
If C does not have any last names then D=BOLD.
And so on.
I've trying conditionally formatting making up formula is... but nothing
works.
Column A
XL 2003
Windows XP.
THANKS.

 

[Shane Devenshire]

Hi,

Lest assume that the last names are in A2:A100 with a title in cell A1, then

To conditionally format your cell(s):

In 2003:
1. Select the cells you want to format (here A2:A100)
2. Choose Format, Conditional Formatting
3. Choose Formula is from the first drop down
4. In the second box enter the formula:
=IF(ROW()=2,TRUE,IF(LEFT(A2)=LEFT(A1),C1,NOT(C1)))
5. Click the Format button
6. Choose a color on the Patterns tab (or any available option)
7. Click OK twice.

In 2007:
1. Highlight all the cells on the rows you want formatted
2. Choose Home, Conditional Formatting, New Rule
3. Choose Use a formula to determine which cell to format
4. In the Format values where this formula is true enter the following
formula:
=IF(ROW()=2,TRUE,IF(LEFT(A2)=LEFT(A1),C1,NOT(C1)))
5. Click the Format button and choose a format.
6. Click OK twice

 

[Lerner]

Hi, Shane, thanks for your help, however only the first name gets to be bold
using the given formula.
Clarification: There are more than one last name starting with letter A
and/or other letters.
Also, not only last names starting with letter C could be missing, it could be
last names starting with any other letter.
ex
A2 ABILA, JOHN
A3 ANARASCO, JESUS
A4 ANTIARO, JANE
A5 CORALIO, MIKE
A6 CORANIO, NICK
In this example last names starting with letter B are missing, like I said
it could be any letter as I will be applying the CF to different
worksheets with different lists.
THANKS.

 

[francis]

try this

1. Select the cells you want to format (assuming here is A2:A10)
2. Choose Format, Conditional Formatting
3. Choose Formula is from the first drop down
4. In the second box enter the formula:
=OR(LEFT(B6)={"A","C","E","G","I","K","M","O"........."Z"})

5. Click the Format button
6. Click Format and choose a color
7. Click OK twice.

Pls note that this formula will not compare the condition on whether
there is a C and if not apply format to D. The formula will apply format to
alternate alpha from A to Z. Otherwise, you will run out of using the if
statement
as excel limit up to 7 level of nested IFs
--
Hope this is helpful

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


Thank You

cheers, francis

 

[Sheeloo]

Following macro will BOLD the first letter on Last Names in Col A in
alternate way as desired by you...

Use this macro;
' See http://www.rondebruin.nl/code.htm if you need help on how to run macros

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

 

[Shane Devenshire]

Hi,

Sorry, lets try this instead - create a dummy column, say for this example
C. In C2 enter the following formula:

=IF(ROW()=2,TRUE,IF(LEFT(B2)=LEFT(B1),C1,NOT(C1)))

Hide this column if desired. Now highlight A2:B100 or wherever your first
and last names are and proceed as in the earlier discussion on conditional
formatting but change the formula to read:

=$C2

You can avoid the dummy column by writing a VBA function but its not worth
the effort in my opinion.

 

[Lerner]

I could live with " bold and not bold A-Z" eventough is not specifically
my goal as explained. However with your formula I get this:
"You may not use unions, intersections, for conditional formating criteria".
Thanks.

 

[Lerner]

Eventough I don't like MACROS, if nothing else will help then:
Where or how I do the pasting of this macro.
I tried right clicking in the SHEET TAB going to VIEW and pasted it there
but did not do anything.
Thanks.

 

[Lerner]

Thanks again Shane, but I got just the first Last name BOLD.

 

[Sheeloo]

Well, one of the ways to avoid macros is not to have requirements which need
macros

To run this macro
Open the worksheet you want it to run on
Press ALT-F11 which will bring up the VB Editor
Double click on the name of the your workbook in the left pane
Click Insert-> Module
Paste the code in the right pane
Press F5 to run the code
Switch to your worksheet..

Next time you can choose Tool->Macros.... Run

 

[francis]

Try with a helper column. say in Z1, which you can hide it,
place the formula provided.

place this formula in the Conditional Formatting
=Z1=TRUE


--
Hope this is helpful

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


Thank You

cheers, francis

 

[francis]

Lerner

Does any of the solutions works for you?
Pls share the outcome if possible.
if not, I will provide you another solution.

--
Hope this is helpful

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


Thank You

cheers, francis

 

[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 prefer conditional formatting, also I'm posting this again in a new post.
Thanks.

 

[francis]

Lerner

It is always better to stick to this thread rather than starting a new post
and provide
your feedback on the solutions provided work or not.

Create a helper column, let say col B, try this formula in B2 and copy
as far down as your data is. This is use to control your
conditional formatting in Col A.
You can place this helper column out of the way and hide it.

=IF(LEFT(A2)=LEFT(A1),MOD(B1,2),MOD(B1+1,2))


Select those cells in col A that you want to apply CF
place this formula for Conditional Formatting
=B1=0

This assume that you know how to apply formatting in CF, if not see the
earlier posts which described the process.

This should work as I have tested it for a fair amount of time last nite
Let me know if this is what you want.
Remember to click on the Yes button as your feedback and this will
also help others to search the archive better in future.

--
Hope this is helpful

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


Thank You

cheers, francis