I find this intriguing...but I couldn't get it to work.
I pasted it into the window as instructed but nothing happened...
Is there something that activates it? a doubleclick, or someting else?
My VBA is rudimentary and I find experimenting with things like this useful
in brnging me up the curve...

thanks,


"Ron Rosenfeld" wrote:

On Fri, 13 Apr 2007 17:51:13 -0700, "JEFF" wrote:

Is it possible to have text and an IF function within a cell contain
variable bolding or color formatting?

For example: A1:4 has 4 positive integer values. B1 has the following
formula:

="The yearly gross is " &sum(A1:A4)&". Great work team!!!"

I would like to format the word 'Great' as bold and font color of red.

Is that possible?

Thank you very much!
Jeff



You would need to use a VBA routine, as what you want to do requires a text
string be in the cell.

You could make it a standalone "Sub", attach it to a button, or use it as an
event-triggered routine which would kind of make it automatic.

For the latter, right click on the sheet tab, and paste the following code into
the window that opens. I've made a few additions to your specifications that
you might find interesting (or not). You might want to change some of the
parameters.

================================================
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dSum As Double
Dim rSrc As Range
Dim rDest As Range
Dim sStr As String
Dim i As Long

Set rSrc = Range("a1:a4")
Set rDest = Range("b1")
dSum = Application.WorksheetFunction.Sum(rSrc)


Application.ScreenUpdating = False
Application.EnableEvents = False

Select Case dSum
Case 1 To 10
sStr = "Not Bad Team"
Case 11 To 20
sStr = "Good Work Team"
Case Is 20
sStr = "Great Work Team!!"
End Select

rDest.Value = "The yearly gross is " & dSum & ". " & sStr

i = InStr(1, rDest.Text, "Great")
If i 0 Then
With rDest
.Characters(i, 5).Font.Bold = True
.Characters(i, 5).Font.Color = vbRed
End With
End If

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

================================================
--ron

Was this post helpful to you?

Why should I rate a post?

The macro is triggered by a worksheet change (e.g. entering a value into some
cell).

1. Did you paste it into the window that opens when you right-click on the
worksheet tab, as instructed? (worksheet module) or merely into a regular
module.

2. What is the contents of the cells in rSrc?

3. For testing, you should consider disabling the
application.enableevents=False line



On Tue, 12 Jun 2007 04:46:01 -0700, dave in Toronto
wrote:

I find this intriguing...but I couldn't get it to work.
I pasted it into the window as instructed but nothing happened...
Is there something that activates it? a doubleclick, or someting else?
My VBA is rudimentary and I find experimenting with things like this useful
in brnging me up the curve...

thanks,


"Ron Rosenfeld" wrote:

On Fri, 13 Apr 2007 17:51:13 -0700, "JEFF" wrote:

Is it possible to have text and an IF function within a cell contain
variable bolding or color formatting?

For example: A1:4 has 4 positive integer values. B1 has the following
formula:

="The yearly gross is " &sum(A1:A4)&". Great work team!!!"

I would like to format the word 'Great' as bold and font color of red.

Is that possible?

Thank you very much!
Jeff



You would need to use a VBA routine, as what you want to do requires a text
string be in the cell.

You could make it a standalone "Sub", attach it to a button, or use it as an
event-triggered routine which would kind of make it automatic.

For the latter, right click on the sheet tab, and paste the following code into
the window that opens. I've made a few additions to your specifications that
you might find interesting (or not). You might want to change some of the
parameters.

================================================
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dSum As Double
Dim rSrc As Range
Dim rDest As Range
Dim sStr As String
Dim i As Long

Set rSrc = Range("a1:a4")
Set rDest = Range("b1")
dSum = Application.WorksheetFunction.Sum(rSrc)


Application.ScreenUpdating = False
Application.EnableEvents = False

Select Case dSum
Case 1 To 10
sStr = "Not Bad Team"
Case 11 To 20
sStr = "Good Work Team"
Case Is 20
sStr = "Great Work Team!!"
End Select

rDest.Value = "The yearly gross is " & dSum & ". " & sStr

i = InStr(1, rDest.Text, "Great")
If i 0 Then
With rDest
.Characters(i, 5).Font.Bold = True
.Characters(i, 5).Font.Color = vbRed
End With
End If

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

================================================
--ron

Was this post helpful to you?

Why should I rate a post?





--ron