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

 

[Greg Truby]

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

You flat out cannot do this. You cannot format part of the cells in a formula uniquely. You can only do this in a constant.

You would either need to convert the formula to a constant, as some have done here or else create the appearance of having a formula. You could put the constant portion of the string in the cell and the variable portion in another cell (TEXT(InRisk,"dd mmmm yyyy") in your example) and format this as bold. Then use the camera tool to take picture of the formatted cell and paste the picture in behind the constant text. Take the border off the picture and the appearance will be the same as if you still had your concatenating formula.

 

[Sandeep Warrier]

I this if the start position of the date part and its length can be obtained then

    With ActiveCell.Characters(Start:=X, Length:=Y).Font
        .FontStyle = "Bold"
    End With

should solve your problem

 

[Foxy Lady]

Hi Mark,
No I can't seem to get it to work, I even tried on a whole new worksheet. Did i work for you???

 

[GTO]

Hi Mark,
No I can't seem to get it to work, I even tried on a whole new worksheet. Did i work for you???

Hi,

Yes it worked. I am in Excel 2000. What version are you using?

If 2007 (which I have not used), I believe you have to save the file as .xlsm

Other than that, of course Macros have to be enabled on earlier versions.

Describe how you "installed" the code and how you tested; my bet is its something silly/simple.

Mark

 

[Foxy Lady]

Hi Mark,
Well I open up a whole new document and entered the in A1 That the monkey ate all the appels dated and in B1 I entered a date.
The I right clicked on the sheet and clicked view code.

I copy and paste the whole thing from your remark and pasted it and then hit run.
Nothing happend

I am not sure if I should have typed That the monkey ate all the appels dated because of the value function used.

I am not sure what to do next

 

[Tom Urtis]

See if the explanation on this link, and the downloadable workbook, help you:

http://www.atlaspm.com/faqformatformula.html#31

 

[GTO]

Hi Mark,
Well I open up a whole new document and entered the in A1 That the monkey ate all the appels dated and in B1 I entered a date.
The I right clicked on the sheet and clicked view code.

I copy and paste the whole thing from your remark and pasted it and then hit run.
Nothing happend

I am not sure if I should have typed That the monkey ate all the appels dated because of the value function used.

I am not sure what to do next

Well goodness! Remember I said it would be something simple/silly? You are going about it backwards.

1) Right-Click the sheet's tab, select View Code.
2) Go back to the sheet and type a date in B1
3) Yer done!

See, we are using the sheet's change event to fire the macro. Specifically, when B1 changes, A1 gets our custom msg (with the date bolded) plastered in it.

Please tell me it works now, I am starting to have self-doubt issues and may need counseling !

Mark

 

[GTO]

...

1) Right-Click the sheet's tab, select View Code.
2) Go back to the sheet and type a date in B1
3) Yer done!

ACK!
1) Right-Click the sheet's tab, select View Code and paste the code...

Sorry 'bout that,

Mark

 

[Foxy Lady]

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

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

 

[Legacy 68668]

Hi seiya,

How can I upload my file so that you can have a look at it

You are not answering to my question.