Combining Text and If statements with varied character (bold, color, etc) formatting.

[JEFF]

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

 

[mrchadmoody]

Hi,

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

 

[Mike Middleton]

mrchadmoody -

Insert | (Tables) PivotTable

- Mike
http://www.mikemiddleton.com

 

[Ron Rosenfeld]

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

 

[Ed Ferrero]

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

Ed Ferrero
www.edferrero.com

 

[Nick Hodge]

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 button>Excel
Options>Customise>Command not on ribbon>PivotTable and PivotChart Wizard)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
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

 

[Roger Govier]

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 wizard>layout 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.

 

[Harlan Grove]

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.

 

[Roger Govier]

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.

 

[Guest]

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,

 

[Don Guillett]

As presented, it was a worksheet_change event. You would have had to
right click sheet tab>view code>copy/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.