Change colour, font and make bold selected upper case letters

[Kippara]

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.

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

 

[Kippara]

After posting it appears bto have lost it's indenting. If this is the case can you suggest how that can be avoided?

 

[Peter_SSs]

Welcome to the MrExcel board!

After posting it appears bto have lost it's indenting. If this is the case can you suggest how that can be avoided?

Yes, read my signature block below. I have fixed your post for you.

Tell us in words what your code is supposed to do. Reading code (that presumably does not work) to determine what is required is a big ask!

If you can give a few sample pieces of data and explain the requirement in relation to those it would also help.

 

[Kippara]

Thank you for your response. It is most appreciated. The following two lines of text have capital letters in them.
"Calculated rate from Standarda Hourly Rate and Item Hourly Rate"
"Entered Job Supply Quantity purchased for job on behalf of customer"
In line 1 there is capital C, S, H, R and I and in Line 2 there is capital E, J, S and Q. I want to be able to select a cell or a range of cells and nominate which letters need to be processed. For instance in line 1 I would only want the S, H, R and in line 2 the J,S and Q to be processed. So in selecting both of these lines all I would want to process are the S, H, R, I, J and Q. The process would be to Change them to the colour Red, make them Bold and to underline them. I would only want the changes to be applied to the cell or range selection.

Book1
	B
3	Calculated rate from Standarda Hourly Rate and Item Hourly Rate
4	Entered Job Supply Quantity purchased for job on behalf of customer
5
6	ends up like this
7
8	Calculated rate from Standarda Hourly Rate and Item Hourly Rate
9	Entered Job Supply Quantity purchased for job on behalf of customer
Sheet1

Looks like there was a problem with the letter colours etc.
This is what it should have looked like so I've used the snipping tool.

Hope this helps. Thanls again

 

[Peter_SSs]

OK, thanks. I think you can do that easily without using Regular Expressions. Try this code with a copy of your data.

Sub HighlightCapitals()
  Dim c As Range
  Dim sList As String, s As String
  Dim i As Long
  
  sList = InputBox("Enter your list. eg YKF")
  If Len(sList) Then
    sList = UCase(sList)
    For Each c In Selection
      s = c.Text
      For i = 1 To Len(s)
        If InStr(1, sList, Mid(s, i, 1), vbBinaryCompare) Then
          With c.Characters(i, 1).Font
            .Color = vbRed
            .Bold = True
            .Underline = True
          End With
        End If
      Next i
    Next c
  End If
End Sub

 

[Kippara]

Tested the code. Works perfectly. Exactly what I wanted. Many thanks.
I note the "Want to help your helpers........" above. Could you advise me how I might do this?

 

[mole999]

Tested the code. Works perfectly. Exactly what I wanted. Many thanks.
I note the "Want to help your helpers........" above. Could you advise me how I might do this?

Follow the links to XL2BB and download the add=in then you can paste formated text that is easier to work with

 

[Peter_SSs]

Follow the links to XL2BB and download the add=in then you can paste formated text that is easier to work with

Did you see the OP's post #4? I think they already have XL2BB.

I note the "Want to help your helpers........" above. Could you advise me how I might do this?

As I have just note to mole999, you have already done that in post #4. Unfortunately, as you found out, XL2BB does not show formatting of individual characters in a cell as you wanted to show.
However, you did show it in that table under the XL2BB mini-sheet and I was able to copy the sample data from the mini-sheet - so you did all you could & it was easy to use your sample data.

 

[mole999]

Did you see the OP's post #4? I think they already have XL2BB.

As I have just note to mole999, you have already done that in post #4. Unfortunately, as you found out, XL2BB does not show formatting of individual characters in a cell as you wanted to show.
However, you did show it in that table under the XL2BB mini-sheet and I was able to copy the sample data from the mini-sheet - so you did all you could & it was easy to use your sample data.

I missed the marker

 

[Kippara]

A question: Is this post public viewing or is that what you are suggesting i.e. Should I post the whole post through XL2BB or just the solution or just a comment on my part?