Bold part of a text with a formula

[Foxy Lady]

Hi there,

I am struggling to get the code for bolding part of a text with a formula.

My sentence is

="That the monkey ate all the appels dated "&TEXT(InRisk,"dd mmmm yyyy")

The date should be in bold

Please help

 

[Legacy 68668]

Assuming B1 has formula like
="That the monkey ate all the appels dated "&TEXT(InRisk,"dd mmmm yyyy")

Private Sub Worksheet_Calculate()
If (Target.Address(0,0) <> "B1") + (Target.Value = "") Then Exit Sub
Application.EnableEvents = False
With Range("b1")
    .Value = .Text
    .Font.Bold = False
    .Font.Underline = True
End With
With CreateObject("VBScript.RegExp")  
    .Pattern = "\d{2} (Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)\D* \d{4}"
    .Global = True
    If .test(Range("b1").Value) Then
        For Each m In .execute(Range("b1").Value))
             Range("b1").Characters(m.FirstIndex + 1, m.Length).Font.Bold = True
             Range("b1").Characters(m.FirstIndex + 1, m.Length).Font.Underline = False
        Next
    End If
End With    
End Sub

 

[GTO]

Hi Seiya,

I'm not overly familiar w/regular expressions, so went to try your suggestion. Since there's no Target argument for Calculate, I thought about using Change, but it didn't look like the first IF test would then be effective.

BTW, when pasting the code, it tossed a syntax error at: For Each m In .execute(Range("b1").Value)) which I believe is just the last closing parenthesis.


Mark

 

[Legacy 68668]

Then try

Private Sub Worksheet_Calculate()
Dim i As Long, Ptn As String, m As Object
Application.EnableEvents = False
With Range("b1")
    .Value = .Text
    .Font.Bold = False
    .Font.Underline = True
End With
For i = 1 To 12
    Ptn = Ptn & "|" & MonthName(i, False)
Next
With CreateObject("VBScript.RegExp")  
    .Pattern = "\d{2}\s" & "(" & Mid(Ptn, 2) & ")\s\d{4}"
    .Global = True
    .IgnoreCase = True
    If .test(Range("b1").Value) Then
        For Each m In .execute(Range("b1").Value)
             Range("b1").Characters(m.FirstIndex + 1, m.Length).Font.Bold = True
             Range("b1").Characters(m.FirstIndex + 1, m.Length).Font.Underline = False
        Next
    End If
End With
Application.EnableEvents = True 
End Sub

 

[GTO]

Hi Mark, It worked perfectly, so how do I get it when the value of B1 is actually in Sheet 2 and needs to be put into sheet 1

Well... I would normally suggest that you would put the code in "Sheet2" 's module, and change:

With Range("A1")

...to...

With ThisWorkbook.Worksheets("Sheet1").Range("A1")

...but given this:

Then also when I need to underline part of a text ex.
The monkeys ate all the apples and/or part of the apples dated 23/03/2009
This should be something like an option button on my userform when I choose All it needs to underline "The monkeys ate all the apples and/or part of the apples dated 23/03/2009" part of the apples should be stroked out. and vise vurse.

Hope you can help me with this.
Basically what my intention is at the end is to cature all the date in Sheet 2 and then give a report based on the information in Sheet 2. Capturing the date will be through a userform

Thx for all the help

I think we need to take a step back and get a more accurate picture of what you want.

Could you give a more detailed set of examples and expound a bit on what you want overall?

Thanks,

Mark

 

[Foxy Lady]

Hi Mark,

Well we have a lot of paper work and I want to minimize it by doing this. Basically I want to capture all the date onto sheet to and then print out a report with the correct things underlined and or bolded. Most of the things are choices ex: either this or that or both. I can't leave out any text and just need to bold and underline the part which are applicable Like in this ex ("The monkeys ate all the apples and/or part of the apples dated 23/03/2009") Here are 3 examples. either the monkeys ate all the apples, or part of the apples or both part and all.

I want to set up an userform that will capture the data onto sheet 2 and on this userform have option buttons for ex all, part or both. By making the choice here the report should then print out the decision that was made.

Can this be done?

Hope you can help

 

[Legacy 68668]

I think I'd better not post any code here.

 

[Foxy Lady]

Hi Seiya,
Sorry for not reply to u, I found that Mark's explanation work so I tried to make sense of it before trying out yours. I will definitely come back to u.

Thank you for helping I will try it out tonight

 

[Foxy Lady]

Hi Seiya,
The code just underline the whole sentence. Am I doing something wrong?

 

[Foxy Lady]

Hi Mark,

Well we have a lot of paper work and I want to minimize it by doing this. Basically I want to capture all the date onto sheet to and then print out a report with the correct things underlined and or bolded. Most of the things are choices ex: either this or that or both. I can't leave out any text and just need to bold and underline the part which are applicable Like in this ex ("The monkeys ate all the apples and/or part of the apples dated 23/03/2009") Here are 3 examples. either the monkeys ate all the apples, or part of the apples or both part and all.

I want to set up an userform that will capture the data onto sheet 2 and on this userform have option buttons for ex all, part or both. By making the choice here the report should then print out the decision that was made.

Can this be done?

Hope you can help