How can I concatenate a bold text, date and time into a specific cell?

Question

Basically, I'm trying to concatenate a specific text (in bold format), the date and time informed in another worksheet into a specific cell. This code below help me to do that:

Sub Atualizar_Abertura()
Dim LN7A As String, LN7B As String
LN7A = "Próximo comunicado:"
LN7B = Planilha3.Range("I3")
Range("D23").Value = LN7A & " " & LN7B
Range("D23").Font.Bold = False
Range("D23").Characters(Start:=1, Length:=Len(LN7A)).Font.Bold = True
Planilha5.Rows("23").AutoFit
End Sub

And this is the result

But the source cell has the NumberFormat "dd/mm/yyyy hh:mm AM/PM [BR]" to be shown as:

So, what I'm expecting is: Concatenate the text as it's already being done and Date/time with the NumberFormat that I used into the cell I3. I exemplified in an image:

Is there any way to do it?

Answer 1

You have a few options. Here's 3 of them:

1. Use `.Text`

.Text gives you a string representing what is displayed on the screen for the cell. Using .Text is usually a bad idea because you could get #### [if the column is not wide enough to display the full text]

^source

You can use it to read from the cell like this:

LN7B = Planilha3.Range("I3").Text

2. Use `Format()` with hardcoded format string

As suggested by T.M. in the comments, you can use

LN7B = Format(Planilha3.Range("I3"), "dd/mm/yyyy hh:mm AM/PM ""[BR]""")

That has the advantage of avoiding issues where you could have "####" appear instead of the real date, but the inconvenient is that if the formatting of the cell changes, the output won't change.

3. Use `Format()` that changes based on the cell's formatting

LN7B = Format(Planilha3.Range("I3").Value2, Planilha3.Range("I3").NumberFormatLocal)

By using this approach, you are always sure that you'll get the same custom formatting as the cell the value came from. Note that there might be exceptions with some built-in number format such as discussed in this question.