Kippara
New Member
- Joined
- Mar 7, 2020
- Messages
- 13
- Office Version
- 2019
- Platform
- 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.
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: