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.

  • 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

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.

  • 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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