Find bolded words in cell and change to upper case letters

[jassie22]

Good day,

I have a database with a lot of bold words (not all of them), I now need these words to change to upper case letters and unbold them. Is there a way to do this with a VBA?

 

[Rick Rothstein]

Are or could these bold words be mixed in with non-bold words all within the same cell?

 

[jassie22]

Are or could these bold words be mixed in with non-bold words all within the same cell?

yes

 

[jassie22]

Are or could these bold words be mixed in with non-bold words all within the same cell?

To better explain. I created a database with food labeling information and I made all allergens bold. Now I want to change this to make ale the allergens in Capital letters/ Upper case. So I would like the macro to:

* Find the words in bold
* Change to upper case
* Unbold the word
* loop Through the database and do this for all the bold words.

Thank you in advance!!!

 

[Norie]

Try this, adjust the range as needed.

Sub MakeBoldUpper()
Dim rng As Range
Dim cl As Range
Dim ch As Object
Dim idx As Long

    Set rng = Range("A1:A10")
        
    For Each cl In rng.Cells
        For idx = 1 To cl.Characters.Count    
            If cl.Characters(idx, 1).Font.Bold Then
                cl.Characters(idx, 1).Text = UCase(cl.Characters(idx, 1).Text)
                cl.Characters(idx, 1).Font.Bold = False
            End If    
        Next idx        
    Next cl
    
End Sub

 

[jassie22]

Try this, adjust the range as needed.

Sub MakeBoldUpper()
Dim rng As Range
Dim cl As Range
Dim ch As Object
Dim idx As Long

    Set rng = Range("A1:A10")
       
    For Each cl In rng.Cells
        For idx = 1 To cl.Characters.Count   
            If cl.Characters(idx, 1).Font.Bold Then
                cl.Characters(idx, 1).Text = UCase(cl.Characters(idx, 1).Text)
                cl.Characters(idx, 1).Font.Bold = False
            End If   
        Next idx       
    Next cl
   
End Sub

It did not work I got the following error:

Error 1004 during execution: Property Text of Class Characters cannot be set.

 

[Norie]

Did you change anything?

 

[jassie22]

Did you change anything?

I changed the range to ("C2:J2") just to test ik on the first row of the database. The range actually should be ("C2:J3500").

 

[Rick Rothstein]

Just so you know, Norie's code worked fine for me using range C2:J3500.

 

[jassie22]

Perhaps because there are some blank cells in the database?