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
Bookmarks