Change font color for variable as part of text in cell

Question

I am struggling with VBA macro which should color part of the text.

The macro looks like

Sub Note()
        Dim c As Range
        Dim val As String
        Set c = ActiveCell
        val = InputBox("Add note", "Note text")
            If IsEmpty(c.Value) = True Then
                c.Value = Format(Now(), "DD MMM YY Hh:Nn") & ": " & val
            Else
                c.Value = c.Value & Chr(10) & Format(Now(), "DD MMM YY Hh:Nn") & ": " & val
        End If
        End Sub

And I want to achieve that Now() will be red and rest of text will be green.

I tried to play with .Font.Color = vbRed etc but without any luck

I also look on this answer but it not quite what I wanted

Answer 1

Try like this:

Option Explicit

Sub Note()

    Dim c           As Range
    Dim val         As String: val = "vit"
    Dim lngLen      As Long

    Set c = ActiveCell
    c.Value = Format(Now(), "DD MMM YY Hh:Nn") & ": " & val
    lngLen = Len(Format(Now(), "DD MMM YY Hh:Nn"))

    c.Characters(Start:=1, Length:=lngLen).Font.Color = vbRed

End Sub

I have removed the input box, but you can return it easily. It gives probably what you want.Pretty much, it asks for the length of the Now() format and it colors the first N signs in the formula in red, following the logic from the question you have mentioned in your question.