Replace a format of a part of a cell

  • Thread starter Thread starter Faraz A. Qureshi
  • Start date Start date
F

Faraz A. Qureshi

Any idea how to have just a single word or part of text of a cell for example
"Amount" wherever occurring on the sheet be converted to be in red colored
font?
 
Hi,

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
If InStr(UCase(Target.Value), "AMOUNT") = 0 Then Exit Sub
With Target.Characters(Start:=InStr(UCase(Target.Value), "AMOUNT"),
Length:=6).Font
.ColorIndex = 3
End With
End Sub

Mike
 
or if you wanted to check an already populated sheet try this

Sub missive()
For Each c In ActiveSheet.UsedRange
If InStr(1, c.Value, "AMOUNT", vbTextCompare) > 0 Then
With c.Characters(Start:=InStr(1, c.Value, _
"AMOUNT", vbTextCompare), Length:=6).Font
.ColorIndex = 3
End With
End If
Next
End Sub

Mike
 
XClent!
--
Best Regards,

Faraz


Mike H said:
or if you wanted to check an already populated sheet try this

Sub missive()
For Each c In ActiveSheet.UsedRange
If InStr(1, c.Value, "AMOUNT", vbTextCompare) > 0 Then
With c.Characters(Start:=InStr(1, c.Value, _
"AMOUNT", vbTextCompare), Length:=6).Font
.ColorIndex = 3
End With
End If
Next
End Sub

Mike
Click to expand...
 
Back
Top