complex cell formatting using VBA

  • Thread starter Thread starter jon
  • Start date Start date
J

jon

I can manually format a cell (from within Excel) so that some of the words
in the cell are bold and others are not.

I want to be able to do this programmatically, from VBA, but cannot locate
any Help or sample code to do this. Can anyone provide some VBA code that
will format the first word in a cell as bold while leaving the remainder
not?

Thanks!

Jon

jbondy at sover dot net
 
Hi
to give you an idea:
sub foo()
dim i as integer
dim rng as range
set rng = selection
i = application.WorksheetFunction.Find(" ",rng)
rng.Characters(1, i-1).Font.Bold = True
end sub

will format the first word of cell A1 as bold
 
Just to add to Frank's code.

Tom Ogilvy's(I think) code will do Column A contiguous cells. Also turns the
first word to Upper Case JFTHOI.

Sub BoldFirstWord()
Dim rng As Range, cell As Range
Dim iloc As Long
Set rng = Range(Cells(1, 1), _
Cells(1, 1).End(xlDown))
rng.Font.Bold = False
For Each cell In rng
iloc = InStr(cell.Formula, " ")
If iloc = 0 Then
If Len(Trim(cell.Formula)) > 1 Then
cell.Font.Bold = True
cell.Formula = UCase(cell.Formula)
End If
Else
cell.Formula = UCase(Left(cell.Formula, iloc - 1)) & _
Right(cell.Formula, Len(cell.Formula) - iloc + 1)
cell.Characters(Start:=1, length:=iloc - 1). _
Font.FontStyle = "Bold"
End If
Next
End Sub

Gord Dibben Excel MVP
 
Back
Top