Formating Parts

[Khalil Handal]

Hi,
I have the following in cell C4 in sheet2:
="text 1 "&Sheet1!b3&" another text 2"
The cell C4 has three parts and can have ONE format for the font.
Is there a way to have the part taken from Sheet1 cell B3 to have different
formating than the other 2 parts?
To be accurate to have it Bold and in different font?

Khalil

 

[Gord Dibben]

Khalil

Not as the result of a formula.


Gord Dibben MS Excel MVP

 

[Ron Rosenfeld]

Hi,
I have the following in cell C4 in sheet2:
="text 1 "&Sheet1!b3&" another text 2"
The cell C4 has three parts and can have ONE format for the font.
Is there a way to have the part taken from Sheet1 cell B3 to have different
formating than the other 2 parts?
To be accurate to have it Bold and in different font?

Khalil

You would have to convert the formula to a text string. Then you could use
different formats on different characters. If this is a dynamic cell, you'd
probably be best off using a Workbook event VBA macro.

Something like:

===============================
Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Const sT1 As String = "text 1 "
Const sT2 As String = " another text 2"
Dim stMid As String

If Sh.Name = "Sheet1" Then
If Not Intersect(Target, Range("B3")) Is Nothing Then
stMid = Worksheets("Sheet1").Range("B3").Text
With Worksheets("Sheet2").Range("C4")
.Value = sT1 & stMid & sT2
.Font.Bold = False
.Characters(Len(sT1) + 1, Len(stMid)).Font.Bold = True
End With
End If
End If

End Sub
======================================

might work. This must be entered as a Workbook Module; and not as an Ordinary
module.
--ron

 

[Khalil Handal]

Hi,
I will try it.
What do you mean by "dynamic cell"??

Khalil

 

[Khalil Handal]

Hi,
Open the VBA
select: this workbook
click on insert then click on Module
I have module2 under modules
pasted your code
BUT nothing happend in the sheets!!
Is ther something that I miss????

Khalil

 

[Gord Dibben]

Khalil

The code posted is worksheet event code.

Right-click on the sheet tab and "View Code".

Paste the code into that sheet module.


Gord

 

[Ron Rosenfeld]

Is ther something that I miss????

"This must be entered as a Workbook Module; and not as an Ordinary
module."

So you need to click on "ThisWorkbook" for your project, and paste the code in
there.
--ron

 

[Khalil Handal]

Thanks to both of you.

"This must be entered as a Workbook Module; and not as an Ordinary
module."

So you need to click on "ThisWorkbook" for your project, and paste the
code in
there.
--ron

 

[Gord Dibben]

Thanks for the correction Ron.

Too many of these popping up. Am I experiencing burn-out or just old age?<g>

Gord

 

[Ron Rosenfeld]

Thanks for the correction Ron.

Too many of these popping up. Am I experiencing burn-out or just old age?<g>

Gord

You Know You're Getting Old When...

You're asleep, but others worry that you're dead.

You have a party and the neighbors don't even realize it.

You can live without sex, but not without glasses.

Your back goes out more than you do.

You quit trying to hold your stomach in, no matter who walks into the room.

You are proud of your lawn mower.

Your best friend is dating someone half their age... And isn't breaking any
laws!

You sing along with the elevator music.

You would rather go to work than stay home sick.

You enjoy hearing about other people's operations.

You consider coffee one of the most important things in life.

You no longer think of speed limits as a challenge.

People call at 9 pm. And ask, "Did I wake you?"

You take a metal detector to the beach.

You wear black socks with sandals.

You know what the word equity means.

You can't remember the last time you laid on the floor to watch television.

Your ears are hairier than your head.

You talk about "good grass" and you're referring to someone's lawn.

You get into a heated argument about pension plans.

You got cable for the weather channel.

You can go bowling without drinking.
--ron

 

[Gord Dibben]

I do believe all apply to me<g>

And I had to quit after the 4th hole this morning when my hip gave out.


Gord