Formatting certain text within a cell

[Guest]

I've used conditional formatting in Excel but I couldn't find a way to
automatically format only parts of a cell. Is there a way using VBA or
formulas in Excel to key on certain words in a cell and format them (ex--> if
"dog" is typed, add today's date to end of "dog" and underline it), leaving
other words untouched?

Also,is there a way to auto line break and resize to fit large blocks of
text that are pasted in a cell?

 

[JE McGimpsey]

One way:

Put this in your worksheet code module (right-click the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sDATEFORMAT As String = "dd mmm yyyy"
Dim rCell As Range
With Target(1)
Application.EnableEvents = False
For Each rCell In Intersect(.Cells, Range("A1:A10"))
With rCell
.Font.Underline = False
.Value = .Text & Format(Date, sDATEFORMAT)
.Characters(Len(.Text) - Len(sDATEFORMAT) + _
1).Font.Underline = True
End With
Next rCell
Application.EnableEvents = True
End With
End Sub


change the range and date format to suit.

 

[Guest]

Bill,
Just turn macro recording on to see the code you need to have to format
different parts of text in a cell. Here is some example code that the macro
recorder produced.

ActiveCell.FormulaR1C1 = "dog 01/1/05"
With ActiveCell.Characters(Start:=1, Length:=4).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=5, Length:=7).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3
End With

As for large chunks of text pasted into a cell, if the WrapText property is
set to true then the row height automatically adjusts to display the text(the
column width does not change). However, if the row height has already been
fixed to a value other than the default, then even with wordwrap=true, the
row height will not change and the text will display only to the extent
possible given the column width and row height.

Alok Joshi