Formatting certain text within a cell
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? -- Thanks,Bill |
Formatting certain text within a cell
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. In article , "Bill" wrote: 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? |
Formatting certain text within a cell
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 "Bill" wrote: 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? -- Thanks,Bill |
All times are GMT +1. The time now is 10:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com