font style of worksheet functions
 

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

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


"gvm" wrote in message
...
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

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

"Bernie Deitrick" wrote:

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


"gvm" wrote in message
...
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

font style of worksheet functions
 

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.
;
Thanks
Eddy



"gvm" wrote:

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

"Bernie Deitrick" wrote:

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


"gvm" wrote in message
...
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

font style of worksheet functions
 

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

One option is to paste specialvalues then manually format the result.


Gord Dibben MS Excel MVP

On Fri, 20 Jan 2006 10:07:02 -0800, "Eddy"
wrote:

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.
;
Thanks
Eddy



"gvm" wrote:

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

"Bernie Deitrick" wrote:

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


"gvm" wrote in message
...
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