Formatting text in Bold at CONCATENATE formula

[shankarsrb]

I typed a paragraph using CONCATENATE formula in cell (B11:I16). On that paragraph four data has been taken from other cell; (M4), (M5), (M6) & (M7) on the same sheet. I want to make Bold of the four cell text value, used in my Concatenate formula from M4, M5, M6 & M7.

Need help me on my tropic.... its urgent.

 

[Fluff]

Hi & welcome to the board.
You cannot have different formats within a value returned by a formula.

 

[shankarsrb]

Can it be done through VBA ? I want to make Bold of the text taken from other cell (M4, M5, M6 & M7)

 

[Fluff]

What is the formula that you are using?

 

[shankarsrb]

=CONCATENATE("We have examined the sheet of"," ",M4," ","of"," ",M5," ","as on"," ",M6," ",M7)

Its a concatenate formula written in cell B11:I16 (merged & wrap text center)

 

[Rick Rothstein]

Here some event code that should work...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("M4:M7")) Is Nothing Then
    Range("B11").Value = "We have examined the sheet of " & [M4] & " of " & [M5] & " as on " & [M6] & " " & [M7]
    Range("B11").Characters(31, [Len(M4)]).Font.Bold = True
    Range("B11").Characters(35 + [Len(M4)], [LEN(M5)]).Font.Bold = True
    Range("B11").Characters([LEN(B11)-LEN(M6&M7)], [LEN(M6&M7)+1]).Font.Bold = True
  End If
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Fluff]

How about

Sub BoldText()
   Dim ln(4 To 7) As Long, i As Long
   For i = 4 To 7
      ln(i) = Len(Range("M" & i))
   Next i
   
   With Range("I11")
      .Value = .Value
      .Characters(31, ln(4)).Font.Bold = True
      .Characters(35 + ln(4), ln(5)).Font.Bold = True
      .Characters(42 + ln(4) + ln(5), ln(6) + 1 + ln(7)).Font.Bold = True
   End With
End Sub

 

[shankarsrb]

Thanks sir for your reply.

In my paragraph the cell value of M4, M5, M6 & M7 has been used two to three times, so i need a VBA code which shows the text in Bold format whenever i used the cell value of M4, M5, M6 & M7 in my B11 cell paragraph.

 

[Rick Rothstein]

Thanks sir for your reply.

In my paragraph the cell value of M4, M5, M6 & M7 has been used two to three times, so i need a VBA code which shows the text in Bold format whenever i used the cell value of M4, M5, M6 & M7 in my B11 cell paragraph.

So the formula you showed us in Message #5 in not actually the formula that will necessarily be in the cell then?

You need to realize when you ask a question in a forum (whether it be this one or another one), the volunteers who answer questions here know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. If you tell us "this is what my data looks like", that is what we will use to develop our solution. And if you hide information from us (like you are now telling us above), I can pretty much guarantee our solutions will not work with it.

 

[shankarsrb]

Thanks sir for the guidelines.

I am not very familiar with VBA codes, so might be i made mistake on specifying my required data, i apologies for that.

I only want to highlight the data taken from M4, M5, M6 & M7 in BOLD whenever it is used in my paragraph at cell C11:I16 (merged and wrap text centre) in my CONCATENATE formula. Thanks.