Is it possible to colour a partial text in VBA???

[agasi]

Is it possible to change a font colour only for partial texts within the cell?? For example, in cell: PLEASE DO SUBMIT in black font colour. I change SUBMIT to ENTER. So PLEASE DO stay in black font colour but ENTER font colour in RED. In my current workbook, the vba turns whole texts to Red. PLEASE DO ENTER in red.

<tbody>
</tbody>

 

[My Aswer Is This]

Show us your current Vba script.

 

[agasi]

Sorry.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim theCell As Range
Dim theArea As Range
Dim theColumn As Integer

theColumn = 3

For Each theArea In Target.Areas
For Each theCell In theArea.Cells
If (theCell.Column = theColumn) Then
theCell.Font.ColorIndex = 3
theCell.Font.Bold = True
End If
Next theCell
Next theArea

 

[chicagocomputerclasses]

theCell.Characters(11, 5).Font.ColorIndex = 3

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C:C")) Is Nothing Then
If Target.Value = "PLEASE DO SUBMIT" Then Target.Font.Color = vbBlack
If Target.Value = "PLEASE DO ENTER" Then
Target.Font.Color = vbBlack
Target.Characters(11, 15).Font.Color = vbRed
End If
End If
End Sub

 

[MickG]

Another option:-
NB:- Change any word in any cell in "A1:A50", then new Word Font changed to Red font

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] oVal [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant, oSp [COLOR="Navy"]As[/COLOR] Variant, Lg [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]If[/COLOR] Not Intersect(Range("A1:A50"), Target) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
oVal = Target
oSp = Split(oVal, " ")
[COLOR="Navy"]With[/COLOR] Application
  .EnableEvents = False
        .Undo
        Sp = Split(Target, " ")
        Target = oVal
        [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Sp)
            [COLOR="Navy"]If[/COLOR] Not Sp(n) = oSp(n) [COLOR="Navy"]Then[/COLOR]
                Target.Characters(Lg + n + 1, Len(oSp(n))).Font.Color = vbRed
                [COLOR="Navy"]Exit[/COLOR] For
            [COLOR="Navy"]End[/COLOR] If
            Lg = Lg + Len(Sp(n))
        [COLOR="Navy"]Next[/COLOR] n
   .EnableEvents = True
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[agasi]

Mick

Apologies. I just gave up on this and did not realise that I have got an answer from you. It is exactly what I need. Really appreciated.

 

[MickG]

You're welcome