Results 1 to 7 of 7

Thread: Making specific text in range of cells bold.

  1. #1

    Making specific text in range of cells bold.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a Project Spreadsheet with multiple projects. Column F contains the Project Descriptions. In column F there are often one or more of the following: "Prior Description," "No Description," or "New Description." I would like to make the quoted text bold. Here is an example of one cell.

    Prior Description: Additional 3000 m2 of new, high quality glasshouses for high throughput phenotyping to support GM activities in ABCD. New Description: Whatís driving it: ABCD ABCD plan. Please check with Frank. We are suspending planning for ABCD at this point.
    What it accomplish: n.a.
    Why thatís important: n.a.

    Above is how I want it to look. Presently none of the text is bold. There are several thousand projects and some added daily so going cell by cell is very time consuming. I have tried every formatting trick I know and zilch. VBA is not my thing, but I suspect if there is a solution, that's where it will be.

  2. #2
    Hi chipgiii
    all depends on the content and layout of the text but try..
    Code:
    Option Explicit
    Sub HighlightCells()
        Dim Lookin As Range, ff As String
        Dim i As Long
        Dim Fnd As Variant
        Dim fCell As Range
        Dim ws As Worksheet
        Dim xItem As Variant
        
        Fnd = Array("Prior Description", "No Description", "New Description")
        For Each ws In Worksheets
            With Sheets(ws.Name)
                For Each xItem In Fnd
                    Set Lookin = .Cells.Find(xItem, Lookin:=xlValues, LookAt:=xlPart)
                    If Not Lookin Is Nothing Then
                        ff = Lookin.Address
                        Do
                           Lookin.Characters(InStr(1, Lookin, xItem), Len(xItem)).Font.Bold = True
                            Set Lookin = .Cells.FindNext(Lookin)
                        Loop Until ff = Lookin.Address
                    End If
                    Set Lookin = Nothing
                Next
            End With
        Next
    End Sub

  3. #3
    or
    Code:
    Option Explicit
    Sub BoldWords()
        Dim Match, ws As Worksheet
        Dim rng As Range
        With CreateObject("vbscript.regexp")
        For Each ws In Worksheets
            For Each rng In ws.UsedRange
                .Pattern = "(Prior Description|No Description|New Description)"
                .IgnoreCase = False
                .Global = True
                If .test(rng.Text) = True Then
                    For Each Match In .Execute(rng.Text)
                        rng.Characters(Match.FirstIndex + 1, Match.Length).Font.Bold = True
                    Next
                End If
            Next
        Next
        End With
    End Sub

  4. #4
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    374
    Articles
    0
    Excel Version
    2020
    Or

    Code:
    Sub M_snb()
        sn = Filter([transpose(If(iserr(search("description",F1:F1000)),"~",row(F1:F1000)))], "~", 0)
        
        For j = 0 To UBound(sn)
            Cells(sn(j), 6).Characters(1, InStr(Cells(sn(j), 6), "description") + 11).Font.Bold = True
        Next
    End Sub

  5. #5
    missed that bit just in any worksheet and in column f
    repeated and multiple occurances of "Prior Description" and/or "No Description" and/or "New Description"
    Code:
    Option Explicit
    Sub BoldWords()
        Dim Match, ws As Worksheet
        Dim rng As Range
        With CreateObject("vbscript.regexp")
        For Each ws In Worksheets
            For Each rng In ws.Range("F1", ws.Cells(Rows.Count, 6).End(xlUp))
                .Pattern = "(Prior Description|No Description|New Description)"
                .IgnoreCase = False
                .Global = True
                If .test(rng.Text) = True Then
                    For Each Match In .Execute(rng.Text)
                        rng.Characters(Match.FirstIndex + 1, Match.Length).Font.Bold = True
                    Next
               End If
            Next
        Next
        End With
    End Sub

  6. #6
    I was out for a few days and just tried this one. Works perfectly! I will try some of the others as well. Thanks a million, and I am very impressed!

  7. #7
    thanks for posting it was an interesting question

Posting Permissions

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