Help with a formula..
 

I am trying to create a formula that will take information from a cell on one
sheet and combine it with text on another sheet. I know how to get the two
together. My problem is that I want the part that is brought in to be bolded
type. Here is what I have in the formula. ="we are pleased to submit our
quotation for "&(cell reference)&" according to the following
specifications." What I want to do is have the cell reference part be bold
type. Is there a way to do that? It doesnt work if I bold the cell.. already
tried it.. Any suggestions?

Thanks!
KK

You'd need to use an event macro instead of a formula. One way:

Put this in your worksheet code module (right-click the worksheet tab
and choose View Code):

Private Sub Worksheet_Calculate()
Const sPLEASED As String = "We are pleased to submit our " & _
"quotation for # according to the following specifications."
Dim nPos As Long
Dim sRef As String

nPos = InStr(sPLEASED, "#")
sRef = Sheets("Sheet2").Range("J10").Text
With Range("A1")
.Font.Bold = False
.Value = Application.Substitute(sPLEASED, "#", sRef)
.Characters(nPos, Len(sRef)).Font.Bold = True
End With
End Sub



In article ,
"Kasey Kern" wrote:

I am trying to create a formula that will take information from a cell on one
sheet and combine it with text on another sheet. I know how to get the two
together. My problem is that I want the part that is brought in to be bolded
type. Here is what I have in the formula. ="we are pleased to submit our
quotation for "&(cell reference)&" according to the following
specifications." What I want to do is have the cell reference part be bold
type. Is there a way to do that? It doesnt work if I bold the cell.. already
tried it.. Any suggestions?

I put that into the sheet and nothing happened.. Is there something that I
need to do to get that macro to run?

"JE McGimpsey" wrote:

You'd need to use an event macro instead of a formula. One way:

Put this in your worksheet code module (right-click the worksheet tab
and choose View Code):

Private Sub Worksheet_Calculate()
Const sPLEASED As String = "We are pleased to submit our " & _
"quotation for # according to the following specifications."
Dim nPos As Long
Dim sRef As String

nPos = InStr(sPLEASED, "#")
sRef = Sheets("Sheet2").Range("J10").Text
With Range("A1")
.Font.Bold = False
.Value = Application.Substitute(sPLEASED, "#", sRef)
.Characters(nPos, Len(sRef)).Font.Bold = True
End With
End Sub



In article ,
"Kasey Kern" wrote:

I am trying to create a formula that will take information from a cell on one
sheet and combine it with text on another sheet. I know how to get the two
together. My problem is that I want the part that is brought in to be bolded
type. Here is what I have in the formula. ="we are pleased to submit our
quotation for "&(cell reference)&" according to the following
specifications." What I want to do is have the cell reference part be bold
type. Is there a way to do that? It doesnt work if I bold the cell.. already
tried it.. Any suggestions?

How about?


"right-click the worksheet tab
and choose View Code):"

Then paste it there




--
Regards,

Peo Sjoblom

(No private emails please)


"Kasey Kern" wrote in message
...
I put that into the sheet and nothing happened.. Is there something that I
need to do to get that macro to run?

"JE McGimpsey" wrote:

You'd need to use an event macro instead of a formula. One way:

Put this in your worksheet code module (right-click the worksheet tab
and choose View Code):

Private Sub Worksheet_Calculate()
Const sPLEASED As String = "We are pleased to submit our " & _
"quotation for # according to the following specifications."
Dim nPos As Long
Dim sRef As String

nPos = InStr(sPLEASED, "#")
sRef = Sheets("Sheet2").Range("J10").Text
With Range("A1")
.Font.Bold = False
.Value = Application.Substitute(sPLEASED, "#", sRef)
.Characters(nPos, Len(sRef)).Font.Bold = True
End With
End Sub



In article ,
"Kasey Kern" wrote:

I am trying to create a formula that will take information from a cell
on one
sheet and combine it with text on another sheet. I know how to get the
two
together. My problem is that I want the part that is brought in to be
bolded
type. Here is what I have in the formula. ="we are pleased to submit
our
quotation for "&(cell reference)&" according to the following
specifications." What I want to do is have the cell reference part be
bold
type. Is there a way to do that? It doesnt work if I bold the cell..
already
tried it.. Any suggestions?

That is what I did. I saved the sheet.. nothing happens.. I enabled the
macros too. anything else?

"Peo Sjoblom" wrote:

How about?


"right-click the worksheet tab
and choose View Code):"

Then paste it there




--
Regards,

Peo Sjoblom

(No private emails please)


"Kasey Kern" wrote in message
...
I put that into the sheet and nothing happened.. Is there something that I
need to do to get that macro to run?

"JE McGimpsey" wrote:

You'd need to use an event macro instead of a formula. One way:

Put this in your worksheet code module (right-click the worksheet tab
and choose View Code):

Private Sub Worksheet_Calculate()
Const sPLEASED As String = "We are pleased to submit our " & _
"quotation for # according to the following specifications."
Dim nPos As Long
Dim sRef As String

nPos = InStr(sPLEASED, "#")
sRef = Sheets("Sheet2").Range("J10").Text
With Range("A1")
.Font.Bold = False
.Value = Application.Substitute(sPLEASED, "#", sRef)
.Characters(nPos, Len(sRef)).Font.Bold = True
End With
End Sub



In article ,
"Kasey Kern" wrote:

I am trying to create a formula that will take information from a cell
on one
sheet and combine it with text on another sheet. I know how to get the
two
together. My problem is that I want the part that is brought in to be
bolded
type. Here is what I have in the formula. ="we are pleased to submit
our
quotation for "&(cell reference)&" according to the following
specifications." What I want to do is have the cell reference part be
bold
type. Is there a way to do that? It doesnt work if I bold the cell..
already
tried it.. Any suggestions?

Did you cause a calculation (F9)?

In article ,
"Kasey Kern" wrote:

That is what I did. I saved the sheet.. nothing happens.. I enabled the
macros too. anything else?