highlight / color change, a particular character in a cell of a worksheet

[biju1011]

Dear Sir,

ACCOR99KKK ACCORD98PQR BB498655I
CCO58965KK ACTIO3259LK9I FG99FGG5
ACCOR99KKK ACCORD98PQR FF559GGT
CCO58965KK ACTIO3259LK9I CDR56789
ACCOR99KKK ACCORD98PQR NJU999FFI
CCO58965KK ACTIO3259LK9 KKOI994F9

The above are 18 cells are in a worksheet, how do I highlight / change color of the character " 9 "  alone. (Not the cell )  Please help

[#2]

Hi biju1011

here is the vba code you can use for the purpose. This would colour red all characters of '9'. Please also see the attached file for an example, where you can change the character string and the colour on the worksheet. 

Sub ChangeIncellCharacterColour()

    Dim rSelectedRange As Range
    Dim vMyCharacter As Variant
    Dim c As Integer
    Dim rCell As Range
    Dim lColourIndex As Long
   
    On Error GoTo HandleErrors
   
    vMyCharacter = 9
    lColourIndex = -16776961
   
    Set rSelectedRange = Selection
   
    'Loop all cells in the selected
    For Each rCell In rSelectedRange
       
        'Loop characters in the current cell
        For c = 1 To Len(rCell)
            'Set colour of the character if it's value matches our parameter
            If rCell.Characters(c, Len(vMyCharacter)).Text = vMyCharacter Then
                rCell.Characters(c, Len(vMyCharacter)).Font.Color = lColourIndex
            End If
        Next c
    Next rCell  
HandleErrors:

End Sub

Hope this is helpful. Please hit 'like' if you like my answer :) 

Kind regards

Yury 

[#3 · Accepted solution]

Thanks for the reply. I am not expert in macros or VB.  one more doubt, instead of red , how can change the colour codes to  green, blue, yellow. Thanks for your help 

[#4]

Unfortunately, using VBA is the only way to solve your task. I have modified the code to use the RGB colours. You can access RGB colours by right clicking a cell, then select Format Cells->Font->More Colors->Custom. 

Sub ChangeIncellCharacterColour()

    Dim rSelectedRange As Range
    Dim vMyCharacter As Variant
    Dim c As Integer
    Dim rCell As Range
    Dim iRed As Integer, iGreen As Integer, iBlue As Integer
   
    On Error GoTo HandleErrors
   
    vMyCharacter = Application.Range("rngMyCharacter")
    iRed = Range("rngRed")
    iGreen = Range("rngGreen")
    iBlue = Range("rngBlue")
   
    Set rSelectedRange = Selection
   
    'Loop all cells in the selected
    For Each rCell In rSelectedRange
       
        'Loop characters in the current cell
        For c = 1 To Len(rCell)
            'Set colour of the character if it's value matches our parameter
            If rCell.Characters(c, Len(vMyCharacter)).Text = vMyCharacter Then
                rCell.Characters(c, Len(vMyCharacter)).Font.Color = RGB(iRed, iGreen, iBlue)
            End If
        Next c
    Next rCell
   
HandleErrors:

End Sub

Please also see attached the modified example file

Hope this helps

Kind regards

Yury