+ Reply to Thread
Results 1 to 4 of 4

Code not formatting strings

Hybrid View

  1. #1
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,397

    Code not formatting strings

    Attached has text in Col A in Bold, text in Col B in italics, and text in Col C as standard.

    For each row, if there is text in Col A and Col D is blank, concatenate Cols A - C into Col D. That works perfectly.

    Then need to find in Col D the string from Col A and make it Bold, and the string from Col B and make that Italic.

    But Code throws a RunTime error 424 at that point.

    Example of desired result shown in F2

    Option Explicit
    Dim a As Long, r As Long
    Dim cl As Range, searchText As Range
    Dim findText As String, findText2 As String
    
    Sub FORMATBLEND()
    
        With Sheet1
        a = .Cells(.Rows.Count, "A").End(xlUp).Row
        If a < 2 Then a = 2
            
        For r = 2 To a
        
    'If there is text in COl A
        If Not Cells(r, 1) Is Nothing Then
        
    'If Col D is blank, concatenate Cols A - C
            If .Cells(r, 4).Value = "" Then
            .Cells(r, 4) = .Cells(r, 1).Value & " " & .Cells(r, 2).Value & " " & .Cells(r, 3).Value
        
    
    
            findText = .Cells(r, 1)
            findText2 = .Cells(r, 2)
        
                If InStr(.Cells(r, 4).Value, findText) Then
    'Make Col A string Bold and Col B string Italic - throw runtime error 424
                InStr(.Cells(r, 4).Value, findText).Font.Bold = True
    'Make Col B string Bold and Col B string Italic
                InStr(.Cells(r, 4).Value, findText2).Font.Italic = True
                End If
            
                    
            
            End If
        
        End If
        
        Next
        
        End With
        
    End Sub
    All solutions, suggestions or alternatives welcome as ever.

    Ochimus
    Attached Files Attached Files

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Code not formatting strings

    Ochimus

    InStr returns the position of the substring you are looking for, you can use that along with Characters to do the formatting.
    Option Explicit
    Dim a As Long, r As Long
    Dim cl As Range, searchText As Range
    Dim findText As String, findText2 As String
    
    Sub FORMATBLEND()
    
        With Sheet1
            a = .Cells(.Rows.Count, "A").End(xlUp).Row
            
            If a < 2 Then a = 2
            
            For r = 2 To a
                
                'If there is text in COl A
                If Not Cells(r, 1) Is Nothing Then
                    
                    'If Col D is blank, concatenate Cols A - C
                    If .Cells(r, 4).Value = "" Then
                        .Cells(r, 4) = .Cells(r, 1).Value & " " & .Cells(r, 2).Value & " " & .Cells(r, 3).Value
                                                                
                        findText = .Cells(r, 1)
                        findText2 = .Cells(r, 2)
                        
                        If InStr(.Cells(r, 4).Value, findText) Then
                            'Make Col A string Bold and Col B string Italic - throw runtime error 424
                            .Cells(r, 4).Characters(InStr(.Cells(r, 4).Value, findText), Len(findText)).Font.Bold = True
                        End If
                        
                        If InStr(.Cells(r, 4).Value, findText2) Then
                            'Make Col B string Bold and Col B string Italic
                            .Cells(r, 4).Characters(InStr(.Cells(r, 4).Value, findText2), Len(findText2)).Font.Italic = True
                        End If
                                                    
                    End If
                    
                End If
            
            Next
        
        End With
        
    End Sub
    If posting code please use code tags, see here.

  3. #3
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: Code not formatting strings

    Removed... Norie posted same solution already.

    However, if you later delete the values in Column D, the font formatting will stay, so you may want to add something to remove bold/italic from the column or cells prior to setting the new text and its format.
    Last edited by Arkadi; 05-30-2019 at 10:45 AM.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  4. #4
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,397

    Re: Code not formatting strings

    Many thanks to both for prompt response and solution.

    Can now mark this as "solved"

    Ochimus

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replacing VBA code strings by using VBA code?
    By shoba in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-07-2018, 08:17 PM
  2. Swap Specific Text Strings in a Range and Indicate new Strings Not already in Code.
    By liquidmettle in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-17-2018, 01:31 PM
  3. Excel changes formatting of certain strings.
    By dhAndrews in forum Excel General
    Replies: 5
    Last Post: 10-18-2016, 12:55 PM
  4. Help formatting loop of strings
    By danielgri14 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-01-2011, 02:31 PM
  5. VBA Code to find and apply formatting to specific text strings
    By chozen86 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-22-2008, 01:20 AM
  6. Formatting concatenated strings
    By ions in forum Excel General
    Replies: 2
    Last Post: 02-22-2008, 04:14 PM
  7. Formatting Odd Number Strings
    By jtmousel in forum Excel General
    Replies: 5
    Last Post: 10-27-2005, 03:05 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