Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bold items within text string
Hi All,
I found a bit of code on the newsgroup that bolds the text within a cell during a concatenation of several cells: Sub BoldNotesTitle() Dim s As String, sA1 As String, sA2 As String Const s1 As String = "On " Const s2 As String = ", please " Const s3 As String = " funds for a margin call." sA1 = Range("A1").Text sA2 = Range("A2").Text With Range("A4") .Value = s1 & sA1 & s2 & sA2 & s3 .Characters(1 + Len(s1), Len(sA1)).Font.Bold = True .Characters(1 + Len(s1) + Len(sA1) + Len(s2), Len(sA2)).Font.Bold = True End With End Sub I'm trying to take this concept and apply it to a range. I wrote a formula (below) that does the concatenation I need. Somehow I need to take my formula into code and BOLD the cells J$1, K$1, L$1, M$1, N$1. My biggest question is how to I apply this formula to the range H2:H500? Thanks! =IF(ISBLANK(J2),"",J$1&" - "&J2&CHAR(10))&IF(ISBLANK(K2),"",K$1&" - "&K2&CHAR(10))&IF(ISBLANK(L2),"",L$1&" - "&L2&CHAR(10))&IF(ISBLANK(M2),"",M$1&" - "&M2&CHAR(10))&IF(ISBLANK(N2),"",N$1&" - "&N2&CHAR(10)) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bold items within text string
Somehow I need to take my formula into code
and BOLD the cells J$1, K$1, L$1, M$1, N$1. You cannot bold parts of text output by a formula... its either bold all the displayed text or not. You can only bold parts of the text in a cell if that text is a text constant. Rick Rothstein (MVP - Excel) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bold items within text string
Thanks Rick. How about this - since the parts of the text string I
want bolded are constant (ie, column headers J$1:N$1), can I read the column headers in variables, and do a FIND routine to find each column header in the text string and then bold? On Jun 9, 10:56*am, "Rick Rothstein" wrote: Somehow I need to take my formula into code and BOLD the cells J$1, K$1, L$1, M$1, N$1. You cannot bold parts of text output by a formula... its either bold all the displayed text or not. You can only bold parts of the text in a cell if that text is a text constant. Rick Rothstein (MVP - Excel) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bold items within text string
Thanks Rick. How about this - since the parts of the text string I
want bolded are constant (ie, column headers J$1:N$1), can I read the column headers in variables, and do a FIND routine to find each column header in the text string and then bold? You cannot bold individual parts or text generated by a formula... there is no way around that using formulas. If you were willing to turn the calculating of the formula to VB in a Change event procedure, then you could have that procedure inject pure text into the cell and that text, being a constant, could be bolded in parts. Rick Rothstein (MVP - Excel) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bold items within text string
Thanks Rick. I'm certainly willing, but have no idea how to do that!
On Jun 9, 11:36*am, "Rick Rothstein" wrote: Thanks Rick. *How about this - since the parts of the text string I want bolded are constant (ie, column headers J$1:N$1), can I read the column headers in variables, and do a FIND routine to find each column header in the text string and then bold? You cannot bold individual parts or text generated by a formula... there is no way around that using formulas. If you were willing to turn the calculating of the formula to VB in a Change event procedure, then you could have that procedure inject pure text into the cell and that text, being a constant, could be bolded in parts. Rick Rothstein (MVP - Excel) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bold items within text string
Rick,
I created a formula in a cell to do what I wanted, then hard-coded the entire column. Now I have a column of values, with no formulas. I tried to do a simple Replace (ie find Bob and replace with Bob in bold), but found that when it finds the text string I'm trying to replace, it adjusts all contents of the cell. On Jun 9, 11:49*am, Steve wrote: Thanks Rick. *I'm certainly willing, but have no idea how to do that! On Jun 9, 11:36*am, "Rick Rothstein" wrote: Thanks Rick. *How about this - since the parts of the text string I want bolded are constant (ie, column headers J$1:N$1), can I read the column headers in variables, and do a FIND routine to find each column header in the text string and then bold? You cannot bold individual parts or text generated by a formula... there is no way around that using formulas. If you were willing to turn the calculating of the formula to VB in a Change event procedure, then you could have that procedure inject pure text into the cell and that text, being a constant, could be bolded in parts. Rick Rothstein (MVP - Excel)- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bold items within text string
Your example is quite simple and I imagine you need more but here's a start.
Select your range of hard-coded cells and run this macro to bold Bob in all cells. Sub Bold_String() Dim rng As Range Dim cell As Range Dim start_str As Integer Set rng = Selection For Each cell In rng start_str = InStr(cell.Value, "Bob") If start_str Then cell.Characters(start_str, 3).Font.Bold = True End If Next End Sub Gord Dibben MS Excel MVP On Thu, 9 Jun 2011 13:48:49 -0700 (PDT), Steve wrote: Rick, I created a formula in a cell to do what I wanted, then hard-coded the entire column. Now I have a column of values, with no formulas. I tried to do a simple Replace (ie find Bob and replace with Bob in bold), but found that when it finds the text string I'm trying to replace, it adjusts all contents of the cell. On Jun 9, 11:49*am, Steve wrote: Thanks Rick. *I'm certainly willing, but have no idea how to do that! On Jun 9, 11:36*am, "Rick Rothstein" wrote: Thanks Rick. *How about this - since the parts of the text string I want bolded are constant (ie, column headers J$1:N$1), can I read the column headers in variables, and do a FIND routine to find each column header in the text string and then bold? You cannot bold individual parts or text generated by a formula... there is no way around that using formulas. If you were willing to turn the calculating of the formula to VB in a Change event procedure, then you could have that procedure inject pure text into the cell and that text, being a constant, could be bolded in parts. Rick Rothstein (MVP - Excel)- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bold items within text string
Thanks Gord. Yes, reality is a bot more complicated than "Bob"! But
this is a one-time routine, so no need to get more sophisticated with code. Thanks for your help! On Jun 9, 3:23*pm, Gord Dibben wrote: Your example is quite simple and I imagine you need more but here's a start. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
search if a string contains text matching items in a list | Excel Worksheet Functions | |||
how to apply bold within string text | Excel Worksheet Functions | |||
auto bold partial text in a string | Excel Discussion (Misc queries) | |||
Join bold and non-bold text in one cell | Excel Discussion (Misc queries) | |||
How do I ask Excel to count how many items are in BOLD TYPE? | Excel Discussion (Misc queries) |