Color Font on partial text in Cell

LeGator

New Member
Joined
May 19, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
The Cell value will be [ 163 Serviced with 8 ml Inspection Symbol #: 67]
The string Inspection Symbol #: is in all the cells. The value 67 is different in the cells.
I would like the static Inspection Symbol #: and the end variable to be vbRed font


Loop through each cell until empty cell, start at D6
lastRow = destSht.Cell s(destSht.Rows.Count, "D").End(xlUp).Row
MLastRow = 6
'The Range that contains the substring you want to change color

For Each Value In Range("D6:D" & lastRow)
Ustr45 = Value

substr = "Inspection Symbol #: " 'The text you want to change color


sNum = sNum - 1
dNum = dNum - 1
MLastRow = MLastRow + 1
Next Value
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
try something along the lines of
VBA Code:
Sub ColorSomeText()

    Dim lastRow As Long
    Dim cel As Range
    Dim startchar As Long, totalchar As Long

With Sheets("Sheet1")
    lastRow = .Range("D" & .Rows.Count).End(xlUp).Row
    For Each cel In .Range("D6:D" & lastRow)
        totalchar = Len(cel.Value)
        startSpot = InStr(cel.Value, " Inspection Symbol #")
        cel.Characters(Start:=startSpot, Length:=totalchar - startchar).Font.Color = vbRed
    Next cel
End With


End Sub
 
Upvote 0
Solution
try something along the lines of
VBA Code:
Sub ColorSomeText()

    Dim lastRow As Long
    Dim cel As Range
    Dim startchar As Long, totalchar As Long

With Sheets("Sheet1")
    lastRow = .Range("D" & .Rows.Count).End(xlUp).Row
    For Each cel In .Range("D6:D" & lastRow)
        totalchar = Len(cel.Value)
        startSpot = InStr(cel.Value, " Inspection Symbol #")
        cel.Characters(Start:=startSpot, Length:=totalchar - startchar).Font.Color = vbRed
    Next cel
End With


End Sub
Worked Perfect. Thanks
 
Upvote 0
Glad to have helped and thanks for reporting back.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top