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]

In order to help you will need to show us the EXACT formula that you are using.

 

[Rick Rothstein]

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.

What I wrote in Message #9 applies to any question you ask in a forum whether requiring a VBA solution or not.

As to your question, the following macro will replace the formula (the code does not need to know your exact formula to work) that you have in the merged cells C11:I16 with a text constant and then bold the words from the cells in M4:M7 within it. The reason the formula is replaced by the text the formula is displaying as a text constant is, as Fluff explained in Message #2 , only text constants can have differing formats within the text inside a single cell or a merged set of cells.

[table="width: 500"]
[tr]
	[td]Sub MakeCertainTextBold()
  Dim X As Long, Z As Long, Txt As String, Cell As Range, BoldMe As Variant
  BoldMe = Range("M4:M7")
  Txt = UCase(" " & Range("C11").Value & " ")
  With Range("C11")
    .Font.Bold = False
    .Value = .Value
    For Z = 1 To 4
      For X = 1 To Len(Txt)
        If Mid(Txt, X, 2 + Len(BoldMe(Z, 1))) Like "*[!0-9A-Za-z]" & UCase(BoldMe(Z, 1)) & "[!0-9A-Za-z]*" Then
          .Characters(X, Len(BoldMe(Z, 1))).Font.Bold = True
        End If
      Next
    Next
  End With
End Sub[/td]
[/tr]
[/table]

 

[shankarsrb]

Thanks sir for your code. It works nicely when i run macro . But the macro replace my formula into a text format, so if i change any value in cell M4 to M7 it doesn't change. (you already mention about the changes, that the macro will replace my formula).

Now why i was doubting to post my full CONCATENATE formula, when you ask me - (as which concatenate formula paragraph should i post ?)
I have 10-15 different paragraph CONCATENATE formula used in different sheets, has same data range in every sheet from cell M4:M7, (all sheets have same cell range, B11:I16), but have different characters / words, so i was thinking to have an unique VBA code to use on every sheet to make the text BOLD where data shows of M4:M7, and also want to keep my concatenate formula, as if i change any value in M4:M7 it should changed the text or value of that paragraph sentence. But maybe i was wrong to think about it, only "Characters" formula can be used to make it happen, and for that I may need 10-15 different VBA characters codes for each sheets.

All the 10-15 paragraph sentence have different characters, so a unique VBA characters formula may also not worked; I have to post 10-15 times for every sentence code, for each concatenate formula, which will somehow became a headache for you people who generously answering my question and solving them without any demand.

Anyway i am posting a concatenate formula (which is one of the longest characters sentence) for which i need two different VBA code to make it BOLD;
(1) only M4 and M7 cell value will be in BOLD letters.
(2) all text contain value from M4:M7 in BOLD letters.

=CONCATENATE("We have examined the attached Balance Sheet of"," ","""",M4,""""," ","of"," ",M5," ","as on"," ",M6," ","and the attached Income & Expenditure Accounts and Receipts & Payments account for the period from"," ",M7,"."," ","We have obtained all the information & explanation required for the purpose of audit.")

Hope to get an reply from your end. Thanks for your valuable time and support.

 

[Fluff]

If you want to keep the formula, one option would be to put the formula in B10 & then use this modification to Rick's code

Sub MakeCertainTextBold()
  Dim X As Long, Z As Long, Txt As String, Cell As Range, BoldMe As Variant
  BoldMe = Range("M4:M7")
  Txt = UCase(" " & Range("B10").Value & " ")
  With Range("B11")
    .Font.Bold = False
    .Value = .Offset(-1).Value
    For Z = 1 To 4
      For X = 1 To Len(Txt)
        If Mid(Txt, X, 2 + Len(BoldMe(Z, 1))) Like "*[!0-9A-Za-z]" & UCase(BoldMe(Z, 1)) & "[!0-9A-Za-z]*" Then
          .Characters(X, Len(BoldMe(Z, 1))).Font.Bold = True
        End If
      Next
    Next
  End With
End Sub

Whenever you change the values in M4:M7 just re-run the macro