[SOLVED] Partial value font color in red using vba

Partial value font color in red using vba

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.

Update: The following code is very useful.

Sub Macro1()

'Following code makes "over" text as red color.

For i = 1 To 100
    If ActiveSheet.Range("A" & i).Value Like "*I love stackoverflow*" Then
        ActiveSheet.Range("A" & i).Characters(InStr(1, ActiveSheet.Range("A" & i).Value, "over", 1), 4).Font.Color = vbRed
    End If
Next i

End Sub

Solution

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