Format a Portion of a Cell Containing a Formula

[redhots4]

I have the following formula in Sheet2!02:
="Class Dates: "&TEXT(Sheet2!B1,"mmm dd, yyyy")&" & "&TEXT(Sheet2!B1+1,"mmm dd, yyyy")

This returns: Class Dates: Aug 03, 2011 & Aug 04, 2011

I need ONLY "Aug 03, 2011 & Aug 04, 2011" to be bold, underlined and be of Times New Roman 14 pt font. The words "Class Dates: " should remain Times New Roman 12 pt regular font.

Any ideas? I don't mind tossing a bit of VB code on the sheet but can't figure out how to do this.

Thanks in advance!

 

[Tom Urtis]

Can't be done fior a cell that contains a formula. You'd need to have the value be a static constant in order to format that portion of it.

This is not to say you cannot achieve the visual effect of what you want, just that it cannot be done in a formula-containing cell.

When the date changes in cell B1 of Sheet2 you can capture that change with a sheet-level Change event (assuming the date got there by manual entry) which in turn produces the constant value in cell O2 that your formula otherwise would have. Then, in that same Change event procedure, the formatting can take place for the static value that gets produced.

That's the methodology anyway, but bottom line, if cell O2 needs to have a formula, you cannot have the kind of formatting in cell O2 that you speak of.

 

[brettdj]

You cant do it with a formula. You can if you are prepared to convert the formula to a value first as the code below does

Cheers
Dave

Sub FormatCells()
    Dim rng1 As Range
    Dim rng2 As Range
    Set rng1 = Selection
    rng1.Value = rng1.Value
    Application.ScreenUpdating = False
    For Each rng2 In rng1
        If Len(rng2.Value) > 13 Then
            If Left$(rng2.Value, 13) = "Class Dates: " Then
                With rng2.Characters(13, Len(rng2.Value) - 12).Font
                    .Name = "Arial"
                    .FontStyle = "Bold"
                    .Size = 14
                End With
            End If
        End If
    Next
    Application.ScreenUpdating = True
End Sub