Partial value font color in red using vba

Question

I will see (1234.56) Dolar in Black when I run the following code

Sheets("Sheet1").Range("A1").Value = Format(-1234.56, "#,##0.00;[Red](#,##0.00)") & " Dolar"

I would like to see (1234.56) in Red and Dolar in Black.

Please note that characters and characters count is variable.

I mean value could be 123.14 or 12.25 or 1.5 etc.

I mean Dolar could be Euro or Pound or Yen etc.

Answer 1

You have to set the colors by character:

Sub formatSpecial()

Dim v As Double, vF As String
v = -1234.56
vF = format(v, "#,##0.00;(#,##0.00)")


Dim c As Range
Set c = Sheets("Sheet1").Range("A1")
With c
    .Value = vF & " dolar"
    If v < 0 Then
        .Characters(1, Len(vF)).Font.Color = vbRed
    End If
End With
End Sub