Results 1 to 4 of 4

Code not formatting strings

Threaded View

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

    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

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