Change colour, font and make bold selected upper case letters

Kippara

New Member
Joined
Mar 7, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
Please excuse me if I am not doing this the right way but this is my first post so I have a lot to learn.
I am trying to change the font, colour and make bold certain uppercase letters in the current selected cell. I have used some code off your website but as a novice have limited ability to make it work. I was hoping you may be able to give me some pointers on how to achieve this for a range of cells.
My current stumbling block is the Set ExtractCap statement. Any suggestions/help to make this work would be appreciated.

VBA Code:
Sub Change_font_Colour_and_Make_Bold_Selected_Upper_Case_Text()
Dim rngCell As Range
Dim CharCount As Integer
Dim CharBegin As Integer
Dim CharEnd As Integer
Dim strToColour As String
Dim RX As Object
Dim ExtractCap As Object
Set RX = CreateObject("VBScript.RegExp")
    With RX
          .Pattern = "[A-Z]"
          .Global = True
          .IgnoreCase = False
    End With
    Set ExtractCap = RX.Replace(Txt, "[A-Z]")
For Each rngCell In Selection
        CharCount = Len(rngCell)
        CharBegin = InStr(1, rngCell, "ExtractCap")
        CharEnd = InStr(1, rngCell, "ExtractCap")
     With rngCell.Characters(CharBegin, CharCount - CharEnd)
'       Pull out string to check for Capital Letter
        strToColour = rngCell.Characters(CharBegin, CharCount - CharEnd).Text
'       If a Capital colour red
        If InStr(strToColour, "ExtractCap") Then
            .Font.Color = vbRed
        End If
  End With
Next rngCell
End Sub
 
Last edited by a moderator:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,784
Office Version
  1. 365
Platform
  1. Windows
Apart from not using the vba tags around your vba code (which i fixed for you), I'm not suggesting that you do anything differently to what you did.
Your question was clear, you provided sample data with XL2BB that we could copy to test with and you provided a visual image of the expected results with the snipping tool - perfect. (y)
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Kippara

New Member
Joined
Mar 7, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
Pete, it's rare in today's world that you get a satisfactory conclusion to most problems. To get one promptly that solves a problem you've spent hours trying to fix through the generosity of others is most satisfying. Again, thanks for your help.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,784
Office Version
  1. 365
Platform
  1. Windows
You're very welcome. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,112,799
Messages
5,542,574
Members
410,560
Latest member
1ndependent
Top