Formatting substring text in cells, array input not working

[KyleC123]

Hi all, I pinched the below bit of code from somewhere, I can't remember where. It formats designated substrings within the cells you select manually, and works awesome. However, it requires me to manually type in all the substrings in VBA.

I tried to change this to accept values from a range of cells, but it gives me the error: "Invalid procedure call or argument" at the line colored in red (I think). I have colored the original, working code in green, and my attempted substitute in pink. I obviously don't run the old and new code at the same time, it just seemed easiest to display this way.

It seems a pretty straightforward substitution, I have no idea why it throws this error. Anyone have any thoughts and/or solutions? Thanks very much!



Sub FindAndFormatOnlySpecificTextInCell()
Dim r As Range
Dim match As Variant
Dim keywords As Variant

keywords = Range("a1:a20").Value
keywords = Array("Joe", "John", "Jane")

On Error GoTo error_and_exit
Application.EnableEvents = False

With CreateObject("VBScript.RegExp")
.Pattern = "(.*?)(" & Join(keywords, "|") & ")"
.Global = True
.IgnoreCase = True

For Each r In Selection.Cells
If Not r.HasFormula Then
For Each match In .Execute(r)
With match
If Trim(Left(r, .FirstIndex + Len(.Submatches(0)))) <> "" Or _
Trim(Mid(r, .FirstIndex + .Length + 1)) <> "" Then
With r.Characters(.FirstIndex + Len(.Submatches(0)) + 1, Len(.Submatches(1)))
.Font.Size = 12
.Font.Color = RGB(255, 0, 0)
.Font.Bold = True
End With
End If
End With
Next
End If
Next
End With

error_and_exit:
If Err Then MsgBox Err.Number & ": " & Err.Description
Application.EnableEvents = True
End Sub

 

[Rick Rothstein]

This line of code...

keywords = Range("a1:a20").Value

makes the keywords variable a two-dimensional array (20 rows, 1 column). The Join function only works on one-dimensional arrays. This is untested (because I do not do Regular Expressions) so I don't know if the overall code will work with it or not, but changing the above line of code to this...

keywords = Application.Transpose(Range("A1:A20").Value)

will make the keywords variable a proper one-dimensional array which Join can work with.

 

[KyleC123]

Welp...I don't fully understand the distinction between a one column array and a one dimensional array, so I never, ever would have caught that!

It works a treat though. Thanks tons for the quick reply and awesome solution!