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

Hi,

How do I bring up the Pivot Table Wizard in Excel 2007?

mrchadmoody -

Insert | (Tables) PivotTable

- Mike
http://www.mikemiddleton.com


wrote in message
ps.com...
Hi,
How do I bring up the Pivot Table Wizard in Excel 2007?

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

Miss the old Wizard?
{Alt}{d} - {Alt}{p}

Ed Ferrero
www.edferrero.com

Hi,

How do I bring up the Pivot Table Wizard in Excel 2007?

Mike

That doesn't bring up the old 'wizard' which I think is what the OP wants.

If so, to do this add the wizard to the QAT (Office buttonExcel
OptionsCustomiseCommand not on ribbonPivotTable and PivotChart Wizard)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
web: www.nickhodge.co.uk
blog: www.nickhodge.co.uk/blog/

FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007
www.officeusergroup.co.uk

"Mike Middleton" wrote in message
...
mrchadmoody -

Insert | (Tables) PivotTable

- Mike
http://www.mikemiddleton.com


wrote in message
ps.com...
Hi,
How do I bring up the Pivot Table Wizard in Excel 2007?

Hi

Whilst Nick and Ed have given you two methods to bring up the old
wizard, IMHO the new method as indicated my Mike is much easier to use
than the old method.

The field list is clearly visible in a pane, as are the four areas of
Row, Column, Page and Data and it is easier to create your required
layout without having to go back to the wizardlayout each time you want
to make a change.

The only occasion that I have found it necessary to invoke the old
wizard, is if Multiple Consolidation ranges are required, as I have not
yet found a method within XL2007 for achieving this.

--
Regards

Roger Govier


wrote in message
ps.com...
Hi,

How do I bring up the Pivot Table Wizard in Excel 2007?

"Roger Govier" wrote...
Whilst Nick and Ed have given you two methods to bring up the old
wizard, IMHO the new method as indicated my Mike is much easier to
use than the old method.
....

Perhaps once you've figured out how to use it. But in the short run
the OP may find the old wizard faster to use.

Harlan, you may well be right.

However, (again IMO) apart from the fact that MS have renamed Page area
to Report Filter, allocating things to the PT report is much the same.
One major advantage though, is you can see the effect upon your PT as
you move fields between Row, Column and Report filter area on screen
without having to go back through the wizard to Layout, make your
changes and Finish, before you see the effect on the PT itself.

--
Regards

Roger Govier


"Harlan Grove" wrote in message
oups.com...
"Roger Govier" wrote...
Whilst Nick and Ed have given you two methods to bring up the old
wizard, IMHO the new method as indicated my Mike is much easier to
use than the old method.
...

Perhaps once you've figured out how to use it. But in the short run
the OP may find the old wizard faster to use.

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

As presented, it was a worksheet_change event. You would have had to
right click sheet tabview codecopy/paste the code.
Then, put numbers in cells a1:a4. Based on the sum in a1:a4 some of the text
in cell B1 would be red.
You may NOT have this functionality in a formula, ONLY in text.

--
Don Guillett
SalesAid Software

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