Automatically Bold Portion of Text within Cell

[Bold]

I am creating cells that have three lines of text using a
function such as this (=List!C12&CHAR(10)&List!
E12&", "&List!F12&CHAR(10)&List!G12), which returns
company name in the first line, City-state in the second
line, and website in the third line. I want the top line,
or the company name, to be in bold while the next two
lines should be in a regular font.

How can I make this happen?

Thanks in advance, and I must say, I am only a recent user
of this board and really appreciate the time savings of
these suggestions and wish I had known about this sooner.

 

[Bernie Deitrick]

Bold,

Not as long as you have a formula.

The macro below will convert the formula in the activecell to a value, and
bold the first line.

HTH,
Bernie
MS Excel MVP

Sub BoldFirstLine()
ActiveCell.Value = ActiveCell.Value
ActiveCell.Characters(Start:=1, _
Length:=InStr(1, ActiveCell.Value, Chr(10))). _
Font.FontStyle = "Bold"
End Sub

 

[hgrove]

Bernie Deitrick wrote...

Bold,

Not as long as you have a formula.

The macro below will convert the formula in the activecell to a
value, and bold the first line.

...

For an inelegant alternative that retains the formula's recal
capabilities, see

http://www.google.com/[email protected]

 

[Bernie Deitrick]

Harlan and Bold,

One could also use the worksheet's calculate event. For example, to show
the bolded result in cell C3, based on the formula in cell B3:

In the sheet's codemodule:

Private Sub Worksheet_Calculate()
BoldFirstLine Range("B3"), Range("C3")
End Sub

In a regular codemodule:

Sub BoldFirstLine(SourceCell As Range, _
ShowCell As Range)
ShowCell.Value = SourceCell.Value
ShowCell.Font.FontStyle = ""
ShowCell.Characters(Start:=1, _
Length:=InStr(1, ShowCell.Value, Chr(10))). _
Font.FontStyle = "Bold"
End Sub

HTH,
Bernie
MS Excel MVP

 

[hgrove]

Bernie Deitrick wrote...
...

One could also use the worksheet's calculate event. For
example, to show the bolded result in cell C3, based on the
formula in cell B3:

In the sheet's codemodule:

Private Sub Worksheet_Calculate()
BoldFirstLine Range("B3"), Range("C3")
End Sub

Quibble: why bother passing Range("B3") rather than jus
Range("B3").Value? You're not using any other property of B3.

In a regular codemodule:

Sub BoldFirstLine(SourceCell As Range, ShowCell As Range)
ShowCell.Value = SourceCell.Value

...

Yes, one could do this, but one should endeavor to avoid infinite loop
in one's code. The statement

ShowCell.Value = SourceCell.Value

triggers recalculation, so fires the Calculate event handler again
which calls BoldFirstLine again, which runs this statement again, yad
yada yada.

If you write Calculate event handlers that omit bracketing

Application.EnableEvents = False

and

Application.EnableEvents = True

around the rest of your code (aside from variable declarations an
immediate error checking/exits), those event handlers are almos
certainly flawed

 

[Bernie Deitrick]

The statement

ShowCell.Value = SourceCell.Value

triggers recalculation, so fires the Calculate event handler again <snip> runs this
statement again, yada yada yada. <snip>
those event handlers are almost certainly flawed.

Harlan,

Interesting theory, but wrong, given the current problem, and proposed
solution. Showcell is dependent on the formula in Sourcecell, which
concatenates values from other cells. Since no other cell references
ShowCell, the calculate event is fired only when the values in the cells
being concatenated are changed, and then is fired precisely _once_.

It may trigger the change event, but that may be a desired effect, so
disabling events may not be necessary. And since there is no change event
code in the proposed solution, then the point is moot. Remember, we're not
programming applications here, Harlan, simply showing what is possible.

As to the flawed event handlers, are you commenting on Excel events in
general, or the sample code in specific? My code works as stated, with no
other side effects given the limited parameters of the problem and solution.
As to passing the range, that is simply personal preference - it's easier to
modify code if you have the object rather than just one property of that
object. I'm sure that it may take a few pico-seconds longer, but I'm a
patient guy.

Bernie
MS Excel MVP

 

[Harlan Grove]

Interesting theory, but wrong, given the current problem, and proposed
solution. . . .

There are times when you're remarkably thick!

Only if the OP's worksheet containing these B3 and C3 cells contained no
formulas AT ALL that called ANY volatile functions. The nasty thing about
volatile functions (you do know what they are or how to search Google Groups
archives to find out, don't you?) is that they fire EVERY TIME ANY ENTRY IS
MADE in any cell in any worksheet in any open workbook UNLESS calculation
mode is set to manual. But if that were the case, your event handler
wouldn't fire except on manually recalculating.

So, try this genius: enter =TODAY() in cell A1 of a test worksheet, ensure
calculation mode is set to Automatic, enter your macro in a general module,
and enter this slightly modified event handler in this worksheet's class
module.

Private Sub Worksheet_Calculate()
MsgBox "Recalc"
BoldFirstLine Range("B3"), Range("C3")
End Sub

Return to that worksheet and enter anything in cell B3. How many times do
you see the message box?

It's true that if you make the breathtakingly simpleminded assumption that
there would never be anything else in this worksheet that could trigger
recalculation your event handler works as you say it does. Others try to
avoid simplemindedness, but I guess you find it blissful. By all means don't
try to learn anything if you find it's too much effort.

 

[excel-noobie]

Hi All, Apologies for digging up an old thread. This was one of the few places I could find anything like what I need. I am using the above macro and it works well, but it would be great if this worked on a selection rather than a cell. Can anyone help? It would save me hours!! Thanks so much for this, and in advance of any answer from anyone who maybe able to help. Sub BoldFirstLine() ActiveCell.Value = ActiveCell.Value ActiveCell.Characters(Start:=1, _ Length:=InStr(1, ActiveCell.Value, Chr(10))). _ Font.FontStyle = "Bold" End Sub