Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting Based on cell A text with conditions in Cell B | Excel Discussion (Misc queries) | |||
Conditional Formatting based on text within a cell w/ text AND num | Excel Worksheet Functions | |||
Formatting text within a cell | New Users to Excel | |||
Formatting Cell to text | Excel Discussion (Misc queries) | |||
Deleting Rows based on text in cell & formatting cell based on text in column beside it | Excel Programming |