VBA Excel 2016 - Upper Case and Font size lines required...

[Dan1000]

The following code works fine, only I would like to insert a line of code to change the text to Upper case and another line to change the size of the font.

Any help would be much appreciated:



Dim i As Integer


Text(1) = "text1"
Text(2) = "text2"
Text(3) = "text3"


For i = LBound(Text) To UBound(Text)
For Each rCell In Range("A2:m100")
sToFind = Text(i)
iSeek = InStr(1, rCell.Value, sToFind)
Do While iSeek > 0
rCell.Characters(iSeek, Len(sToFind)).Font.Bold = True
rCell.Characters(iSeek, Len(sToFind)).Font.Underline = True
>
>

iSeek = InStr(iSeek + 1, rCell.Value, sToFind)
Loop
Next rCell
Next i


End Sub

 

[Joe4]

In Excel VBA, use the UCASE function to make things upper case. See: https://www.techonthenet.com/excel/formulas/ucase.php

You should be able to use the Macro Recorder to record any formatting changes that you want to make, and get the code from there.

 

[Fluff]

Try

Do While iSeek > 0
With rCell.Characters(iSeek, Len(sToFind)).Font
   .Bold = True
   .Underline = xlSingle
   .Size = 16
End With
   rCell.Replace sToFind, UCase(sToFind)

iSeek = InStr(iSeek + 1, rCell.Value, sToFind)
Loop

 

[Dan1000]

Thank you for the replies, will view yours Joe4. I tried yours Fluff and its doing something but maybe due to my missing the top 3 lines of the Sub that its not working correctly...sorry, the Sub should have been as follows:

Sub Find_and_Bold()
Dim rCell As Range, sToFind As String, iSeek As Long
Dim Text(1 To 3) As String
Dim i As Integer




Text(1) = "text1"
Text(2) = "text2"
Text(3) = "text3"






For i = LBound(Text) To UBound(Text)
    For Each rCell In Range("A2:m100")
            sToFind = Text(i)
            iSeek = InStr(1, rCell.Value, sToFind)
    
    Do While iSeek > 0
With rCell.Characters(iSeek, Len(sToFind)).Font
   .BOLD = True
   .Underline = xlSingle
   .Size = 16
End With
   rCell.Replace sToFind, UCase(sToFind)


iSeek = InStr(iSeek + 1, rCell.Value, sToFind)
Loop
    
    
    
    
    
    
    
    Next rCell
Next i




End Sub

 

[Fluff]

In what way isn't it working?

Also when posting code please use code tags, the # icon in the reply window.

 

[Dan1000]

Hi Fluff

Its raised the text size in a few cells in columns H and M and looks to have underlined most if not all the other text items and has made all text in all cells bold.

Also, can you send an example of the hash symbol in its place which you mention should be used - never done that in the past but would like to keep to any rules there are


Any further help grateful received, thank you

 

[Fluff]

Try it like

   For i = LBound(Text) To UBound(Text)
      For Each rCell In Range("A2:m100")
         sToFind = Text(i)
         iSeek = InStr(1, rCell.Value, sToFind, 1)
         
         Do While iSeek > 0
            rCell.Replace sToFind, UCase(sToFind)
            
            With rCell.Characters(iSeek, Len(sToFind)).Font
               .Bold = True
               .Underline = xlSingle
               .Size = 16
            End With
            
            iSeek = InStr(iSeek + 1, rCell.Value, sToFind)
         Loop
      Next rCell
   Next i

Re the # icon. Along the top of the reply window there are various icons, the last of which is the #, if you click on that you will see two code tags appear. Simply paste your code between them.

 

[Dan1000]

Thats done it, thank you so much for your help Fluff (and advice regarding the code insertion technique)!

Dan

 

[Fluff]

You're welcome & thanks for the feedback