Results 1 to 14 of 14

How to Mark specific text in a pattern BOLD through defined Variable using wildcard

Threaded View

  1. #1
    Forum Contributor
    Join Date
    08-17-2013
    Location
    Asia
    MS-Off Ver
    Excel 2021
    Posts
    273

    How to Mark specific text in a pattern BOLD through defined Variable using wildcard

    My current code loops through range A62:A5000 (fixed / static range), and searches for word "PETER", wherever it find, it marks the text "PETER" Bold.

    Dim xFind As String
    Dim xCell, xTxtRg, xRg As Range
    Dim xCount As Long
    Dim xLen, xStart As Integer
    
    On Error Resume Next
    Set xRg = Activesheet.Range("A62:A5000") 
    'On Error Resume Next
    Set xTxtRg = Application.Intersect(xRg.SpecialCells(xlCellTypeConstants, xlTextValues), xRg)
    
    If xTxtRg Is Nothing Then
    Exit Sub
    End If
    'where specific text found
    xFind = "PETER" 
    xLen = Len(xFind)
        
    For Each xCell In xTxtRg
    xStart = InStr(xCell.Value, xFind)
    Do While xStart > 0
        xCell.Characters(xStart, xLen).Font.Bold = True
        xCount = xCount + 1
        xStart = InStr(xStart + xLen, xCell.Value, xFind)
    Loop
    Next
    What I am now looking is to change the variable content, currently
    xFind = "PETER"
    to something like XFind = PETER SPACE and if this pattern with prefix PETER is found, to format only this portion of PETER + Pattern as bold within entire

    Illustrations
    PETER 4.3x ' no action PETER and anything >=5 with suffix .0x be marked as bold
    PETER 5.2x ' since it is PETER blank space >=5, then entire PETER 5.2x portion be marked bold
    PETER 305.2x ' since it is PETER blank space >=5, then entire PETER 305.2x portion be marked bold
    PETER 2.2x ' since it is PETER blank space <5, then entire PETER 2.2x portion not be formatted

    Content of any cell within Range A62:A5000 would have multiple lines (smaple workbook attached), but idea is to find where PETER + >= 5.0x is found and same (selected text in defined pattern) be formatted as bold

    Without running the above code given in sample workbook, I have manually highlighted to illustrate what objective I want to achieve.

    Sample Data starts from row cell A129
    Attached Files Attached Files
    Last edited by analystbank; 10-04-2023 at 05:00 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Mark Text In A Cell In Bold In Brackets
    By jayfin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-08-2021, 12:36 PM
  2. [SOLVED] Bold SPECIFIC text in range
    By Rafaelpresa123 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-04-2021, 12:31 PM
  3. How to make a specific text bold with in a cell where & formula is used
    By uniquevignesh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-23-2020, 01:42 PM
  4. [SOLVED] Split string variable by specific pattern
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 04-17-2018, 03:07 AM
  5. Replies: 5
    Last Post: 04-06-2014, 08:59 PM
  6. [SOLVED] VBA Bold Specific Text In String
    By hobbiton73 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-28-2014, 08:09 AM
  7. search for specific text pattern within string
    By nzdzy2 in forum Excel General
    Replies: 3
    Last Post: 10-10-2011, 11:47 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1