CODEWITHSUNDEEP
excelvba
PsyduckDebugging
PsyduckDebugging

Reputation: 19

Format only the fragment of text in a cell

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

Upvotes: 0

Views: 216

Answers (1)

JvdV
JvdV

Reputation: 75850

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

enter image description here

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

enter image description here

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

enter image description here

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

Upvotes: 3

Related Questions