Format only the fragment of text in a cell

Question

I'm trying to change format of a part of a number-like content in a cell. For example:

In =1400 & "(∆"& -345 &")", i want only "-345" to be red. -> more or less it's going to be the same number of chars from right.

Conditional Formatting along VBA solutions are welcome :)

Edit - It should be working on formulas i.e. both '1400' and '-345' are formulas, not values

You will need to parse the characters then... one by one... and detect which characters need formatting. — braX, May 13 '20 at 08:45
Does this answer your question? [Change color of certain characters in a cell](https://stackoverflow.com/questions/7618121/change-color-of-certain-characters-in-a-cell) — braX, May 13 '20 at 08:46
@braX I have checked the link you attached, but unfortunetly when I use it on something like =1400&" ("&-345&")" the macro treats every char as numeric. But anyway I got some idea on what should i do. Thanks! — PsyduckDebugging, May 13 '20 at 09:05
Is this always at the same position from the right? And always these 4 characters? — JvdV, May 13 '20 at 09:16
@JvdV Yes, it can work this way. I might do any further fitting — PsyduckDebugging, May 13 '20 at 09:18

JvdV · Accepted Answer · 2020-05-13T10:34:36.093

So if it's always at the same position from the right and always 4 characters, then please try:

With Range("A1")
    .Characters(.Characters.Count - 8, 4).Font.Color = vbRed
End With

Using the second parameter we can specify the length which in your case is 4 characters. That way you won't have to parse characters one by one to format them. For more information please refer to the docs

Would you need to find the position of - from the right, then use InstrRev. This would substitute .Characters.Count - 8 as a starting position.

Edit:

Through the comments it became clear your are actually working with formulas based on other cells. Since you can't really change the font color within a formula, you could hide the actual formula and add a change event to the worksheet. A very basic example could be:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Not Intersect(Target, Range("C2:D2")) Is Nothing Then
    With Range("B2")
        .Value = .Offset(, -1).Value
        .Characters(Len(.Value) - Len(.Offset(, 2).Value), Len(.Offset(, 2).Value)).Font.Color = vbRed
    End With
End If

Application.EnableEvents = True

End Sub

In fact, if you go this route, you could also decide to actually remove the actuall formula alltogether and construct the text to format through VBA:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Not Intersect(Target, Range("C2:D2")) Is Nothing Then
    With Range("B2")
        .Value = .Offset(, 1).Value & "(" & ChrW(8710) & .Offset(, 2).Value & ")"
        .Characters(Len(.Value) - Len(.Offset(, 2).Value), Len(.Offset(, 2).Value)).Font.Color = vbRed
    End With
End If

Application.EnableEvents = True

End Sub

Haven't checked, but wouldn't this only work on static text in a cell and not the result of a formula? It's 8 characters in the formula, but 5 in the result. — Darren Bartrup-Cook, May 13 '20 at 09:57
@DarrenBartrup-Cook, I assumed the formula is static text in my example that's true. — JvdV, May 13 '20 at 09:59
Thanks for the answer but I'm getting "Unable to get Count property of Characters class" error — PsyduckDebugging, May 13 '20 at 10:01
^ Which is the cause that you are probably working with formulas? =) — JvdV, May 13 '20 at 10:02
Would you be okay with changing the formula into a value (text) first? @Sztywny — JvdV, May 13 '20 at 10:08
Well, you can't change the color of characters within a formula. However, what if you hide this column of formulas, then have a second column with static text that would change based on the hidden column. The above macro would then work again.... I'll update the question if you are interested — JvdV, May 13 '20 at 10:15

Format only the fragment of text in a cell

1 Answers1