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:

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Kippara

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,784
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 7, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
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
3Calculated rate from Standarda Hourly Rate and Item Hourly Rate
4Entered Job Supply Quantity purchased for job on behalf of customer
5
6ends up like this
7
8Calculated rate from Standarda Hourly Rate and Item Hourly Rate
9Entered 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.

1583653545977.png


Hope this helps. Thanls again
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,784
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

VBA Code:
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

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

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,784
Office Version
  1. 365
Platform
  1. Windows
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. (y)
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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. (y)
I missed the marker
 

Kippara

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

Watch MrExcel Video

Forum statistics

Threads
1,112,803
Messages
5,542,590
Members
410,561
Latest member
Sasha Lawrence
Top