Results 1 to 7 of 7

Code quit working - will not format key text

Threaded View

  1. #1
    Registered User
    Join Date
    11-28-2013
    Location
    Texas
    MS-Off Ver
    365
    Posts
    4

    Code quit working - will not format key text

    Many thanks to those who manage this forum - you provide a great service. Here are the details of my issue:
    System: Mac OS X, Macbook pro, Office 2011
    Spreadsheet: 5 columns, variable rows but always a header row. Column C holds key words. Column E holds concatenated text that includes at least one instance of the key word in Column C.
    Objective: Search in Column E for the key word that is in Column C and format all instances of the key word only in Column E with bold, italic and underline.
    The sub below ran OK several times but quit working now and will not format the words.
    Any and all help would be greatly appreciated. Thank you.

    Sub Logos_Key_Word()
    'Objective of sub is to search a cell for instances of a key words and apply formatting to those words with bold, italic and underline.
    'Key words are in column C and text to be searched (and where key words are to be formatted) is in column E.
    Dim PosF As Integer, aLen As Integer 'PosF is the position of the first letter of the key word and aLen is the length of the key word.
    Dim PosL As Integer, PosT As Integer 'PosL is the position of the last letter of the key word and PosT is used to test that an instance of the key word is present.
    Dim aWord As String 'aWord is the key word from the current row in column C.
    Dim lRow As Long
    Dim i As Integer
    
        'Count rows with data.
        lRow = Range("A" & Rows.Count).End(xlUp).Row
        For i = 2 To lRow 'Row 1 is a header so start in row 2.
    
        Cells(i, 5).Select
        aWord = ActiveCell.Offset(0, -2).Text 'Get key word from column C.
        aLen = Len(aWord)
        PosF = InStr(Cells(i, 5), aWord) 'Find the position of the first letter of the key word.
        PosT = 0
        
        Do While PosF > PosT 'PosF should be greater than PosT as long as an instance of the key word is found.
        With Cells(i, 5).Characters(PosF, aLen).Font 'Apply the formatting to the characters of the key word.
            .FontStyle = "Bold"
            .Underline = True
            .Italic = True
         End With
         
         PosL = PosF + aLen 'Adjust position variables to check for another instance of the key word.
         PosT = PosT + PosL
         PosF = InStr(PosT, Cells(i, 5), aWord, vbTextcompare)
         
         Loop
        Next i
    
    End Sub
    Attached Files Attached Files
    Last edited by Strepho63; 01-21-2015 at 10:52 AM. Reason: Rule violation and provide more info.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro to change colour of cell based on finding a specific word in the cell
    By Shelby761 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-24-2014, 09:46 AM
  2. Finding text to the right of a word in a cell.
    By Hyflex in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-11-2014, 12:47 PM
  3. Finding a specific word within cell
    By Vespasian in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 01-26-2013, 05:57 AM
  4. Finding certain word in cell....then split text
    By Lynx2x in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-22-2012, 08:09 AM
  5. Finding longest word in a cell
    By contaminated in forum Excel General
    Replies: 1
    Last Post: 06-10-2009, 05:15 PM

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