Forum Discussion
Solved
highlight / color change, a particular character in a cell of a worksheet
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
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
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
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
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
