I currently have a cell with a person's last name a space and the first name.
I have already done the gramatical formatting, adding a comma between last
and first name. I need to know how to format the whole cell to show caps for
the first letter of the last and first names, and then lower case for all
other letters. In addition, I also need to format the last name only to be
bold. Any help appreciated. My macro work is very limited, good on formulas
though.

On Thu, 23 Jun 2005 08:48:02 -0700, "JC" wrote:

I currently have a cell with a person's last name a space and the first name.
I have already done the gramatical formatting, adding a comma between last
and first name. I need to know how to format the whole cell to show caps for
the first letter of the last and first names, and then lower case for all
other letters. In addition, I also need to format the last name only to be
bold. Any help appreciated. My macro work is very limited, good on formulas
though.

The only way I know of to do this (other than manually) is with a macro.
However, you will have to allow the macro to replace any formula you might have
in the cell with the name, with the TEXT value.

If this is not acceptable, then you could put the result in some other,
adjacent cell.

The macro could be set up to run when selected; be attached to a button; or
even run automatically when a change is made in one of the formula result
cells.

First select the cells to process. The macro does check to see that there is a
comma in the cell before processing.

To change the format in the cell where the name is:

====================
Sub FormatName()
Dim c As Range
Dim LenLN As Long

For Each c In Selection
If InStr(1, c.Text, ",") 0 Then
LenLN = InStr(1, c.Value, ",") - 1
With c
.Font.Bold = False
.Value = Application.WorksheetFunction.Proper(c.Text)
.Characters(1, LenLN).Font.Bold = True
End With
End If
Next c

End Sub
=====================

To put the result in an adjacent cell:

===========================
Sub FormatName()
Dim c As Range
Dim LenLN As Long

For Each c In Selection
If InStr(1, c.Text, ",") 0 Then
LenLN = InStr(1, c.Value, ",") - 1
With c
.Offset(0, 1).Font.Bold = False
.Offset(0, 1).Value = Application.WorksheetFunction.Proper(c.Text)
.Offset(0, 1).Characters(1, LenLN).Font.Bold = True
End With
End If
Next c

End Sub
=========================


--ron