VBA to bold all dates within a single cell

[Candyman8019]

Hi all,

I've been struggling to come up with code to bold all dates within a single cell.

My data looks like this:

In this scenario, three dates would be bolded and the rest of the text in the cell would not be.

Any thoughts on how to do this?

 

[BigBeachBananas]

Hi all,

I've been struggling to come up with code to bold all dates within a single cell.

My data looks like this:
View attachment 100220
In this scenario, three dates would be bolded and the rest of the text in the cell would not be.

Any thoughts on how to do this?

Would you ever have another date in the body of the notes? Example: Sep 18, 2023 - Sent message to xxxx xxxx on Sep 15, 2023?

 

[Candyman8019]

Would you ever have another date in the body of the notes? Example: Sep 18, 2023 - Sent message to xxxx xxxx on Sep 15, 2023?

there could be other dates, but I would be okay with those being bolded as well.

 

[Georgiboy]

I was working on the below but I have to go home now. The below will only work if the date is at the begining of a new row within the cell. It may help with finding the solution:

Sub test()
    Dim rng As Range
    Dim rCell As Range
    Dim tVar As Variant, x As Long
   
    Set rng = Range("A1:A3")
   
    For Each rCell In rng
        tVar = Split(rCell, Chr(10))
        For x = 0 To UBound(tVar)
            If IsNumeric(Application.Match(Left(tVar(x), 3), Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"), 0)) Then
                rCell.Characters(InStr(rCell, Left(tVar(x), 12)), 12).Font.Bold = True
            End If
        Next x
    Next rCell
End Sub

Book1
	A
1	Sep 18, 2023 - blah blah. Sep 21, 2023 - jkjlqwdjlqwjjo;jk;. Sep 22, 2023 - jjjajjaja - shhshshh
2	Sep 18, 2023 - blah blah. Sep 21, 2023 - jkjlqwdjlqwjjo;jk;. hdhdhhdhdh Sep 22, 2023 - jjjajjaja - shhshshh
3	Sep 18, 2023 - blah blah. Sep 21, 2023 - jkjlqwdjlqwjjo;jk;. Sep 22, 2023 - jjjajjaja - shhshshh
Sheet1

 

[Candyman8019]

That works perfect! Thanks Georgiboy

 

[Candyman8019]

Okay, one slight issue...if there is more than one comment on the same date, it only bolds the first instance of it.

Thoughts?

 

[Georgiboy]

Give the below a try instead:

Sub test()
    Dim rCell As Range
    Dim regEx As Object, regMatches As Object, regMatch As Object
    
    For Each rCell In Range("A1:A3")
        Set regEx = CreateObject("VBScript.RegExp")
        With regEx
            .Global = True
            .IgnoreCase = True
            .Pattern = "([A-Za-z]{3} \d{2}, \d{4})"
            Set regMatches = .Execute(rCell.Value)
        End With
        For Each regMatch In regMatches
            With regMatch
                rCell.Characters(.FirstIndex + 1, Len(.Value)).Font.Bold = True
            End With
        Next regMatch
    Next rCell
End Sub

 

[Candyman8019]

That did "IT" ! Thanks again Georgiboy.

 

[Georgiboy]

You're welcome, glad we could help.

 

[Candyman8019]

Just an FYI, I had to use both solutions together. The second part didn't bold any dates with single digit days...so putting both pieces together covered all of the bases.