Need help adjusting VBA to change date formats in text.

[skyport]

I am currently using the macro illustrated below, which has been working perfectly fine for a long time. The only problem I'm currently encountering is whenever it is processing data that contains text within sentences and paragraphs, it always changes whatever the current dating format is to xx/yy/zzzz. Even if I format the cells properly and select the desired date format in the Excel document to the date format I want, when the macro runs and produces its result, it always comes out with the undesired date format. I was hoping there was a way to possibly insert something into the macro below that would not change or alter any of its current functions but give the possibility to choose the date format it would produce within the text as desired. It will solve a lot of problems for me. Please let me know if someone can help with this. The basic function of the code, which is working fine and should not be changed, is to concate columns A-G and it makes the first line of text in each cell up to the first occurrence of a period followed by two spaces come out in bold and the rest of the text in each cell not bolded. Once again, my only issue is trying to be able to control the format of dates that show up in the text in each of the cells.

Current code:

Sub Concat()
Dim i As Long, LR As Long, j As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With Range("K" & i)
.Value = .Offset(0, -10).Value & .Offset(0, -9).Value & .Offset(0, -8).Value & _
.Offset(0, -7).Value & .Offset(0, -6).Value & .Offset(0, -5).Value & .Offset(0, -4).Value


j = InStr(.Value, ". ")
.Characters(Start:=1, Length:=j).Font.Bold = True
End With
Next i
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[Andrew Poulsom]

Can you post your VBA code please?

 

[skyport]

Hi Andrew, thanks for helping me. The code is above in the original post under current code.

 

[Andrew Poulsom]

To return what appears in the cell use the Text property rather than the Value property:

        .Value = .Offset(0, -10).Text & .Offset(0, -9).Text & .Offset(0, -8).Text & _
            .Offset(0, -7).Text & .Offset(0, -6).Text & .Offset(0, -5).Text & .Offset(0, -4).Text

 

[skyport]

That did it perfectly. Thanks so much for your kind help. This is what makes Mr. Excel the greatest.