Change the font color for a variable as part of the text in a cell

Question

I am struggling with a VBA macro that should color some 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 ensure that Now () will be red and the rest of the text will be green.

I tried to play with .Font.Color = vbRed etc., but with no luck

I also look at this answer , but that’s not quite what I wanted.

Answer 1

You linked the answer, but you did not use what was there, why?

Try the following:

 Sub Note() Dim c As Range Dim val As String Dim StartChar As Integer, _ LenColor As Integer Set c = ActiveCell val = InputBox("Add note", "Note text") With c .Font.Color = RGB(0, 0, 0) If IsEmpty(.Value) = True Then StartChar = 1 LenColor = Len("DD MMM YY Hh:Nn") .Value = Format(Now(), "DD MMM YY Hh:Nn") & ": " & val .Characters(Start:=StartChar, Length:=LenColor).Font.Color = RGB(255, 0, 0) Else StartChar = Len(.Value) + 1 LenColor = Len("DD MMM YY Hh:Nn") .Value = .Value & Chr(10) & Format(Now(), "DD MMM YY Hh:Nn") & ": " & val .Characters(Start:=StartChar, Length:=LenColor).Font.Color = RGB(255, 0, 0) End If End With 'c End Sub 

Answer 2

Try it 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 deleted the input field, but you can easily return it. This probably gives you what you want. By and large, it asks for the length of the Now () format, and it paints the first N characters in the formula in red, following the logic from the question that you mentioned in your question.

Answer 3

Try the following:

 Sub Note() Dim c As Range Dim val As String Dim lngPos As Integer Set c = ActiveCell val = InputBox("Add note", "Note text") c.Value = "" If IsEmpty(c.Value) = True Then c.Value = Format(Now(), "DD MMM YY Hh:Nn") & " - " & val lngPos = InStr(ActiveCell.Value, " - ") With ActiveCell.Font .ColorIndex = 4 End With With ActiveCell.Characters(Start:=1, Length:=lngPos - 1).Font .ColorIndex = 3 'or .Color = RGB(255, 0, 0) End With Else c.Value = c.Value & Chr(10) & Format(Now(), "DD MMM YY Hh:Nn") & " - " & val lngPos = InStr(ActiveCell.Value, " - ") With ActiveCell.Font .ColorIndex = 4 End With With ActiveCell.Characters(Start:=1, Length:=lngPos - 1).Font .ColorIndex = 3 'or .Color = RGB(255, 0, 0) End With End If End Sub