How to embed font/size changes within Excel formula
Hi
You would need some VBA code to do this.
The following code needs to be copied into a standard module in your
Workbook.
DO THIS ON A COPY OF YOUR WORKBOOK FIRST - just in case!!!!
Press Alt+F11 to start the Visual Basic Editor.
Click InsertModule
In the new module created, copy the code as below and paste into the white
pane.
Press Alt+F11 to go back to your worksheet
On your sheet Press Alt+F8 to bring up macroshighlight BoldFirstRowRun
This has been set to change the font for the first word to Bold Font 12, and
the remainder to Regular font 8
It has been set to start from row 10, of Column E (5). Change the values to
suit the range where you want this to take place.
NOTE: This will change the values in the cells from a formula to a value.
Sub BoldFirstWord()
Dim firstspace As Long, wordlength As Long
Dim len1 As Long, start2 As Long, len2 As Long
Dim cell As Range
Dim row As Long, lastrow As Long, colno As Long, n as long
row = 10 '<--- set to row number for start
colno = 5 '<--- set to column number to use
lastrow = Cells(Rows.Count, colno).End(xlUp).row
For n = row To lastrow
Cells(row, colno).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
firstspace = WorksheetFunction.Find(" ", ActiveCell)
wordlength = Len(ActiveCell.Value)
len1 = firstspace - 1
start2 = firstspace + 1
len2 = wordlength - firstspace
With Selection.Characters(Start:=1, Length:=len1).Font
.Name = "Arial" '<--- set as required
.FontStyle = "Bold" '<--- set as required
.Size = 12 '<--- set as required
End With
With Selection.Characters(Start:=start2, Length:=len2).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
End With
Next n
End Sub
--
Regards
Roger Govier
"Tired of poor table nagivation"
. com wrote in message
...
I want to change from a large font size of the first word in a cell to a
smaller font size for the remainder of words, for cel text that is built
up
multiple text cells via a formula. This type of formatting is quite easy
for
a text (not formula) cell (just select the portion of text you want
different
and change font, size, color, etc.). However, I haven't been able to find
the set of operators/modifiers to accomplish the same thing within a
formula.
Please help. Thanks
|