change first 3 characters to bold format

Question

How do I change the first 3 characters and "CLEARANCE" Font to BOLD of cells containing "T##-" and loop it until the last row of STANDARD and NON-STANDARD tables

Sub Formatting()

    Dim StartCell As Range
    Set StartCell = Range("A15")
    Dim myList As Range

    Set myList = Range("A15:A" & Range("A" & Rows.Count).End(xlUp).Row)
    Dim x As Range

    For Each x In myList
        'myList.ClearFormats
        x.Font.Bold = False
        If InStr(1, x.Text, "CLEARANCE") > 0 Or InStr(1, x.Text, "clearance") > 0 Then
            x.Font.Bold = True
        Else
            x.Font.Bold = False
        End If
    Next
    
        For Each x In myList
        'myList.ClearFormats
        x.Font.Bold = False
        If InStr(1, x.Text, "T*") > 0 Then
            x.Font.Bold = True
        Else
            x.Font.Bold = False
        End If
    Next

End Sub

ORIG

FORMATTED

To change part of a cell's formatting you use the `Characters` collection. — Tim Williams, May 25 '21 at 06:04
Also there is no need to loop ;) Fastest way is you filter your data and work with it. `rng.AutoFilter Field:=1, Criteria1:="=T??-*", Operator:=xlAnd, Criteria2:="=*CLEARANCE*"` — Siddharth Rout, May 25 '21 at 06:09
Also there is another faster way (I believe than using Characters). Let me do some testing before I post an answer. — Siddharth Rout, May 25 '21 at 06:16
Example: `If Left(x, 1) = "T" Then x.Characters(, 3).Font.FontStyle = "Bold"` For bolding the first 3 characters in a string if it starts with "T". — Christofer Weber, May 25 '21 at 06:18
Can you change you question title avoid lower case? Or are you shouting at us? — Solar Mike, May 25 '21 at 06:25
thanks Siddharth Rout I tried your code ,"clearance" only left — gab30, May 25 '21 at 07:12
Not sure what do you mean? See the screenshot. Clearance also gets bold. — Siddharth Rout, May 25 '21 at 07:17

Siddharth Rout · Answer 1 · 2021-05-25T10:29:24.410

Here is one way to achieve what you want which I feel is faster (I could be wrong). This way lets Excel do all the dirty work :D.

Let's say our data looks like this

LOGIC:

Identify the worksheet you are going to work with.
Remove any autofilter and find last row in column A.
Construct your range.
Filter the range based on "=T??-*" and "=*CLEARANCE*".
Identify the filtered range.
Check if there was anything filtered and if it was, then do a Find and Replace
Search for "CLEARANCE" and replace with bold tags around it as shown in the code.
Loop through the filtered range to create an html string and then copy to clipboard
Finally paste them back.

CODE:

Is this what you are trying? I have commented the code so you should not have a problem understanding it but if you do them simply ask :)

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim rng As Range, rngFinal As Range, aCell As Range
    Dim htmlString As Variant
    
    '~~> Set this to the relevant Sheet
    Set ws = Sheet1
    
    With ws
        '~~> Remove any autofilter
        .AutoFilterMode = False
        
        '~~> Find last row in Col A
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        '~~> Construct your range
        Set rng = .Range("A1:A" & lRow)
        
        '~~> Filter the range
        With rng
            .AutoFilter Field:=1, Criteria1:="=T??-*", _
                        Operator:=xlAnd, Criteria2:="=*CLEARANCE*"
                 
            '~~> Set the filtered range
            Set rngFinal = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
        End With
    End With
    
    '~~> Check if there was anything filtered
    If Not rngFinal Is Nothing Then
        rngFinal.Replace What:="CLEARANCE", Replacement:="<b>CLEARANCE</b>", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
        
        '~~> Loop through the filtered range and add
        '~~> ending html tags and copy to clipboard and finally paste them
        For Each aCell In rng.SpecialCells(xlCellTypeVisible)
            If aCell Like "T??-*" Then
                htmlString = "<html><b>" & _
                             Left(aCell.Value2, 4) & "</b>" & _
                             Mid(aCell.Value2, 5) & "</html>"
                
                With CreateObject("htmlfile")
                    With .parentWindow.clipboardData
                        Select Case True
                            Case Len(htmlString): .setData "text", htmlString
                            Case Else: .GetData ("text")
                        End Select
                    End With
                End With
                
                DoEvents
                
                aCell.PasteSpecial xlPasteAll
            End If
        Next aCell
    End If
    
    '~~> Remove any filters
    ws.AutoFilterMode = False
End Sub

OUTPUT:

NOTE: If you want to bold either of the text when one of them is absent then change Operator:=xlAnd to Operator:=xlOr in the above code.

I tried but it says Compile error:Variable not defined at "FormulaVersion:=xlReplaceFormula2" — gab30, May 25 '21 at 07:23
I think you are using an older version of excel. Delete `, FormulaVersion:=xlReplaceFormula2` and try it again — Siddharth Rout, May 25 '21 at 07:24
thanks it works like charm I just removed the "FormulaVersion:=xlReplaceFormula2" — gab30, May 25 '21 at 07:25
Glad it worked :) Quick question. What are your thoughts about the speed on your data? I mean as compared to your original code? Was it fast? — Siddharth Rout, May 25 '21 at 07:26
it loads for a second but it doesnt affects data at all, can I replace "Set ws = Sheet1" with non-specific sheetname? cuz I use different sheetnames — gab30, May 25 '21 at 07:34
I applaud you!! I was thinking about a regex solution with submatches but am still scratching my head over it.... — JvdV, May 25 '21 at 07:42
@JvdV would love to see it come in place. :) Do post it if you figue it out! — Siddharth Rout, May 25 '21 at 09:25

JvdV · Answer 2 · 2021-05-25T10:43:26.507

2

I thought I'd chuck in this solution based on regex. I was fiddling around a long time trying to use the Submatches attributes, but since they do not have the FirstIndex() and Lenght() properties, I had no other option than just using regular matching objects and the Like() operator:

Sub Test()

Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
Dim rng As Range, cl As Range, lr As Long

lr = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Set rng = ws.Range("A1:A" & lr)

With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "\bCLEARANCE\b"
    For Each cl In rng
        If cl.Value Like "T[0-9][0-9]-*" Then
            cl.Characters(0, 3).Font.Bold = True
            If .Test(cl.Value) Then
                Set M = .Execute(cl.Value)
                cl.Characters(M(0).firstindex + 1, M(0).Length).Font.Bold = True
            End If
        End If
    Next
End With

End Sub

The Like() operator is there just to verify that a cell's value starts with a capital "T", two digits followed by an hyphen. This syntax is close to what regular expressions looks like but this can be done without a call to the regex-object.

When the starting conditions are met, I used a regex-match to test for the optional "CLEARANCE" in between word-boundaries to assert the substring is not part of a larger substring. I then used the FirstIndex() and Lenght() properties to bold the appropriate characters.

edited May 25 '21 at 10:43

answered May 25 '21 at 10:00

JvdV

70,606
8
39
70

1

This is beautiful! :) To make it faster combine it with Autofilter? – Siddharth Rout May 25 '21 at 10:03
@SiddharthRout, Thanks, but don't want to steel any thunder from your answer. I do agree we can speed things up here though. One thing I noticed is that you disregarded the line where "CLEARANCE" is absent. Why did you do so if I may ask? – JvdV May 25 '21 at 10:24
2

I was under the impression that both conditions were a must. Can be easily handled with changing `Operator:=xlAnd` with `Operator:=xlOr` :) – Siddharth Rout May 25 '21 at 10:27
@RonRosenfeld, thanks for thinking along. It's literally what I had written down in my initial post before edits, but I figured it's an unnecessary "Or" operator inside the expression when I have used `Like()` operator to validate the start of a cell's value. – JvdV May 25 '21 at 10:54
If both conditions are not a must, then you can use simply `^T\d\d|CLEARANCE` which will give you your matches. – Ron Rosenfeld May 25 '21 at 10:55
If both conditions are a must, I'll have to think about that as VBA regex does not have a look-behind token. – Ron Rosenfeld May 25 '21 at 10:57
@RonRosenfeld, it was exactly my struggle, hence the use of `Like()` operator being a "better" alternative. Note, OP mentioned: "*How do I change the first 3 characters and "CLEARANCE" Font to BOLD of cells containing "T##-"*", meaning to me at least values should start with a capital "T", two digits and an hyphen. – JvdV May 25 '21 at 10:59
I think you are correct. If both conditions are a must, then two tests are necessary. – Ron Rosenfeld May 25 '21 at 11:21
1

*Side note:* the Like comparison `"T[0-9][0-9]-*"` could be reduced to `If cl.value Like "T##-*" Then` (i.e. `#` replacing `[0-9]`) @JvdV – T.M. May 25 '21 at 14:06
@T.M. yessir! Correct =) – JvdV May 25 '21 at 14:44

score 0 · Answer 3 · answered May 25 '21 at 07:42

The short and easy, but not fast and flexible approach. "Bare minimum"
No sheet specified, so uses active sheet. Will ignore multiple instances of "CLEARANCE", will loop everything (slow), ingores starting pattern (only cares if it starts with "T"), doesn't remove any bold text from things that shouldn't be bold.

Sub FormattingLoop()
Dim x As Range
For Each x In Range("A15:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    If Left(x, 1) = "T" Then x.Characters(, 3).Font.FontStyle = "Bold"
    If InStr(UCase(x), "CLEARANCE") > 0 Then x.Characters(InStr(UCase(x), "CLEARANCE"), 9).Font.FontStyle = "Bold"
Next x
End Sub

change first 3 characters to bold format

3 Answers3