Change to Bold repeated text in cell

August 28, 2012 at 12:21:50
Specs: Macintosh
Hello all,

I am trying to write some code that will look in an excel cell find if it contains the word that I am looking for and make it bold. I have written the following code so far

With Worksheets("Label Print").Cells(i, J)

.Characters(Start:=InStr(.Value, “Name”), Length:=Len(“Name”)).Font.Bold = True

End With

The issue is that if “Name” occurs twice (or more) in a cell it will highlight only its first occurrence.

Thank you in advance

See More: Change to Bold repeated text in cell

Report •

August 29, 2012 at 04:16:47
Is J a variable or a column letter? I only ask because it is capatilized. If it's a column letter it needs to be enclosed in quotes.

Please post some examples of your data. When I tried to make up my own strings with the word "Name" in it, your code bolded more than just the word "Name". Maybe it was something with the strings I tried.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

August 29, 2012 at 06:32:55

When I try strings like this:

1  My Dog's Name Is Fido
2  Fido Is What I Call My Dog
3  Name the Dog Fido

The string Name in Rows 1 and 3 will be bolded, but so will Fido in Row 2.

This is because InStr(.Value, “Name”) returns 0 if Name isn't found and the first 4 characters will be bolded.

I don't know if that's a problem for you, but that's what I found to be happening with my data.

Of course, this doesn't address your question, but I thought I'd mention it anyway.

I believe that this code will get you what you want. It's an adaptation of the code found at this site, which counts the occurrences of a pattern of characters in a cell.

By looping through the cell, character by character, looking for 4 character strings that match "Name", it will bold all occurrences.

Just to be efficient, it will skip any cell that doesn't contain the string Name.

With Worksheets("Label Print").Cells(i, J)
   If .Value Like "*Name*" Then
        For c = 1 To Len(.Value)
            If Mid(.Value, c, Len("Name")) = "Name" Then
                .Characters(Start:=c, Length:=Len("Name")).Font.Bold = True
            End If
        Next c
   End If
  End With

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

Ask Question