Formatting text string in formula

Hi,

This is essentially part two to my previous post with the title 'range.formula: text string and cell reference'.

Now that Rick Williams has pointed me in the right direction (told me the answer!) I seem to have another issue..

With a text string you can format each character using the following kind of approach

With Range("Myrange")
    .Characters(1, 7).Font.Bold = True
    .Characters(1, 1).Font.Italic = True
    .Characters(2, 4).Font.Subscript = True
    .Characters(2, 1).Font.Italic = False
    .Characters(3, 1).Font.Italic = True
    .Characters(4, 8).Font.Italic = False

End With

 This doesn't seem to work with formulas that contain both text and a referenced cell value.. Can the characters be formatted from VBA for this kind a situation? (this was the whole point of using VBA as I found I couldn't do the formats in excel directly - as the two formulas were connected in an If statement)

The form of the formula in VBA is (as in the last thread) ..

"=""ABC ""&A1&"" DEF"""

Any help, as always, greatly appreciated!

You should be able to calculate the start and end positions of a cell content by examining the Text property.

Are ABC and DEF known values or are they derived in the code?

What formatting do you ACTUALLY want to apply?

Ha ha!  I hunted down your other thread. :)

This is probably a question for the more wider readers, but in my little bit of testing, I've found that I can't set character-by-character formatting for a cell that contains a formula or a number - I can only do it for cells that contain a literal string.

This may be what Bob was alluding to in his post, but if you don't expect the value of your function to change after your macro is run (ala your previous post), you could consider setting the value of the cell directly (i.e. it would not be linked to the value of A1 anymore.

Rather than use 

  Range("A11").Formula = "=""ABC ""&A1&"" DEF"""

use

  Range("A11").Value = "ABC " & Range("A1").Value & " DEF"

Oh, and another suggestion put to me my a colleague here is if you want it to still link, but want the different formatting, you could separate it into adjacent cells:

Put ABC in A11 (and make it bold)

Put =A1 in B11

Put DEF in C11  (and make it italic)

then change the column widths to suit.  Even set the cell borders to white to disguise the fact that they're separate.

I need some quote about illusions here...

Hi, Thanks for the replies and suggestions..

ABC and DEF are known, fixed values.  Letters in fact.  Between the two sets of letters is a number, which will change at various times after the macro is run.  I want to make the whole lot bold and then within that, the A a capital, B a subscripted italic lowercase, the C a subscripted capital (non italics).  The number will be a two digit value, also in bold.  The second set of letters just in bold.

The background to this is that this string is providing the result.  Some index letters infront, the result value and then the units.

I've applied the text string version in the locations where the values won't change once the macro has been run - works great.

In this instance the result will change, and in fact the macro puts in the relevant index values in for the given calculation - i.e. dependent on info elsewhere it chooses the correct formula/result type to use and puts in the cell.

I had this all running from the excel cell, but couldn't get the formatting so thought VBA would be the way forward!

This is an extension to an existing worksheet/calc. so I'm trying to avoid splitting across cells/clolumns as this means alot of mucking about elsewhere with the setting out of other info and print areas etc.  Also, by fitting it all in one cell means it can all be centered together and will basically look better..

If it simply isn't possible I guess I'll have to split it into different columns etc..

or... would turning it into a function work? (i.e. so it could be posted to the cell as a text string but will update if there is any change to the cells it's dependent on?)

Cheers for the help.. keep it coming :-)

Something like this?

With Range("Myrange")
    .Value = "ABC" & Range("A1").Value2 & "DEF"
    .Font.Bold = True
    .Characters(1, 1).Text = UCase(.Characters(1, 1).Text)
    .Characters(2, 1).Text = LCase(.Characters(2, 1).Text)
    .Characters(3, 1).Text = UCase(.Characters(2, 1).Text)
    .Characters(2, 2).Font.Subscript = True
End With

Hi,

Unfortunately that doesn't work. (Unless I'm missing something) it puts a static text string into the cell, which means that the value of A1 doesn't change when A1 changes.

I'm trying to get that formatting applied whilst the cell contains a formula such that when the value of A1 changes it will also change in 'MyRange'.

I can put the correct formula into the cell okay and I can also do the formatting of a text string.  I just can't get the formatting of the text bit whilst it's part of a formula.

thanks for the help though..

cheers,

I think the solution is likely to be to use code like Bob has provided above, but to include it in the worksheet_change event and/or worksheet_calculate - I'm not a morning person, and my brain isn't quite warmed up yet.

That way the (literal string) value in 'myRange' will be updated each time a cell on the sheet is changed/each time the sheet is calculated.  Or if it's appropriate, you could link a subroutine to a button and run it that way.

Cheers,

Rick

You can't do it from the formula you have to use VBA. It can b e tied to worksheet change event  though

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1"     '<== change to suit

    On Error GoTo ws_exit
    Application.EnableEvents = False

    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
        With Range("Myrange")
            .Value = "ABC" & Target.Value2 & "DEF"
            .Font.Bold = True
            .Characters(1, 1).Text = UCase(.Characters(1, 1).Text)
            .Characters(2, 1).Text = LCase(.Characters(2, 1).Text)
            .Characters(3, 1).Text = UCase(.Characters(2, 1).Text)
            .Characters(2, 2).Font.Subscript = True
        End With
    End If

ws_exit:
    Application.EnableEvents = True
End Sub

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

Okay, that looks like the way forward.

Thanks for spending the time on it and helping me out.

I still find it somewhat annoying that Excel doesn't let you format in this way!

It seems like overkill that the code has to be looked at anytime there's a change on the worksheet.. seems rather inefficient.

Oh well... you never know - MS may sort it out...

Thanks again

Highly unlikely. I doubt they even see it as a problem, and most certainly don't see it as a bug, so unless their data tells them that millions of people are having issue with it there is no chance.