font style of worksheet functions

[Guest]

I have a worksheet function as follows: ="the rate is special and equals
" & average(a1:a3)

I want to change the font style and color of the word "special" in the
character string to bold and green. I understand it can be done with event
macros. Thanking you in anticpiation, .... Greg

 

[Bernie Deitrick]

Greg,

Copy the code below, right click the sheet tab, select "View Code", and paste the code in the window
that appears.

In another cell, put the formula
=AVERAGE(A1:A3)
to force a calc event whenever a value in A1:A3 changes.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Range("A4").Value = "The rate is special and equals " _
& Format(Application.Average(Range("A1:A3")), "0.00") _
& "."
With Range("A4").Characters(Start:=13, Length:=7).Font
.FontStyle = "Bold"
.ColorIndex = 50
End With
Application.EnableEvents = True
End Sub

 

[Guest]

excellent Bernie, thank you. Now I'm having trouble applying same sort of
solution to a more complex problem but I will post that under a new question,
thanks again ... Greg

 

[Guest]

Bernie,

How do I change the excel font style functions?
I have a function ="Helo "&(O30) and I want to make the content of O30 only
bold.
how to do it? Your help is greatly appreciated.
(e-mail address removed); (e-mail address removed)
Thanks
Eddy

 

[Gord Dibben]

Formulas can only return values......not change formatting.

One option is to paste special>values then manually format the result.


Gord Dibben MS Excel MVP