Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a cell that contains something like this:

="String of text that's moderately long" & YEAR(NOW()) & "."

I want to know if there's any way that, via macro, I can colorize/stylize PART of the resulting string. Like:

String of text [that's] {moderately} <long/2013.>

with the part in square brackets in red, the part in curly braces in blue, and the part in <> underlined.

I already tried this:

Private Sub Workbook_Open()
    With Sheet1
        With .Cells(8, 1).Characters(25, 9).Font
            .Color = vbRed
        End With

        With .Cells(8, 1).Characters(34, 3).Font
            .Color = vbBlack
        End With

        With .Cells(8, 1).Characters(37, 9).Font
            .Color = vbBlue
        End With

        With .Cells(8, 1).Characters(47).Font
            .Color = vbBlack
            .Underline = True
        End With
    End With
End Sub

and while it works, it only seems to work correctly on fixed content cells, not cells with a formula like I have. So yeah, any ideas?

I'm not completely averse to filling the cell entirely from VBA, in case that's what it comes to.

share|improve this question
    
Thats not a duplicate. The linked method Characters(start, length) doesn't work in this scenario since its not a fixed value. The question title needs to be adjusted to reflect the difference. –  nixda Feb 4 '13 at 17:49
    
@nixda Question reopened. Feel free to suggest an edit to address that. –  slhck Feb 4 '13 at 21:58

1 Answer 1

up vote 2 down vote accepted

There are two ways to describe the contents of cell:

  1. The actual contents and
  2. the apparent visual contents after the spreadsheet is evaluated after a calc.

For a string or fixed contents, these are the same. But for a formula they are completely different. Your VBA code is dealing with the contents of the cell (1), not the result (2). So, yes, you'll have to retrieve the contents from your formulas and move them to fixed content cells, where your vba formatting will work.

Options are to use a hidden row for the formulas, and a visible row for the actual display. If the omission of a row number by using a hidden row reference is too awkward/visible for the intended user, using a separate sheet might be an alternative.

share|improve this answer
    
I see. Thanks for the reply, it explained a lot. =) I ended up going the code monkey way and filling + formatting the entire cell via VBA when the document is opened. Thanks for the explanation! –  fbordas Feb 4 '13 at 8:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.