VBA - Colour code a string of text

[kialexander]

Hi,

I am currently using the vba code below to turn the sentence "Deal capacity over 95%" to red, however, the issue I am facing is that it turns all text in that cell red, not just the statement "Deal capacity over 95%". Is there a way I can stop the whole cell text turning red and just turn the statement "Deal capacity over 95%" red instead?

Columns("H:H").Select
With Application.ReplaceFormat.Font
.Superscript = False
.Subscript = False
.Color = 255
.TintAndShade = 0
End With
Selection.Replace What:="Deal capacity over 95%", _
Replacement:="Deal capacity over 95%", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=True
Range("H6").Select


Many Thanks,

Kirstie

 

[wideboydixon]

You need to apply the formatting to the actual characters in the string. This would achieve what you want I think:

Public Sub HighlightSearchString()

Const SearchString = "Deal capacity over 95%"

Dim firstCell As Range
Dim nextCell As Range

Set firstCell = Range("H:H").Find(SearchString, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False)
If firstCell Is Nothing Then Exit Sub
Set nextCell = firstCell
Do
    nextCell.Characters(Start:=InStr(1, nextCell.Value, SearchString, vbTextCompare), Length:=Len(SearchString)).Font.Color = 255
    Set nextCell = Range("H:H").FindNext
Loop Until nextCell.Address = firstCell.Address

End Sub

WBD