I set out to do what I thought would be a simple macro and hit not one, but
two walls. The macro was to simply tack the date on to the current (text)
contents of a cell and then leave that cell open (i.e. in edit mode) with the
cursor ready for input after the date. The two walls that I hit were (1)
leaving the cell open for input, and (2) retaining the changing font color
formatting of the cell (often phrases or sentences in the cell are different
colors from the rest).

Are there reasonably easy ways around these two problems? I thought of
using a simple form with just a text box instead of allowing the editing
directly in the cell. That could solve the first problem but I still have
the second.

Thanks,
Will

Will,

For problem 2, check out the following link and see if it helps in your
case. I did a search on "change font color part of text" and got a few
good results.

Alan



http://groups.google.com/group/micro...of+text&qt_g=1

roadkill wrote:
I set out to do what I thought would be a simple macro and hit not one, but
two walls. The macro was to simply tack the date on to the current (text)
contents of a cell and then leave that cell open (i.e. in edit mode) with the
cursor ready for input after the date. The two walls that I hit were (1)
leaving the cell open for input, and (2) retaining the changing font color
formatting of the cell (often phrases or sentences in the cell are different
colors from the rest).

Are there reasonably easy ways around these two problems? I thought of
using a simple form with just a text box instead of allowing the editing
directly in the cell. That could solve the first problem but I still have
the second.

Thanks,
Will

This might be work for you. Put the code in a module or a CommandButton
etc. This inserts the text for you, but you can use existing text too.

Sub ColourAndEdit()
Sheets(1).Range("A1").Select
Selection.Value = "Black RED Black " & Date

With ActiveCell.Characters(Start:=7, Length:=3).Font
.Name = "Arial"
.ColorIndex = 3
End With

Application.SendKeys ("{F2}")
End Sub

As much as I distrust and dislike SendKeys it has got me out of many a
situation where I can't do things 'properly'!

The worksheet must be active/visible during execution for this to work.

wrote:
Will,

For problem 2, check out the following link and see if it helps in your
case. I did a search on "change font color part of text" and got a few
good results.

Alan



http://groups.google.com/group/micro...of+text&qt_g=1

roadkill wrote:
I set out to do what I thought would be a simple macro and hit not one, but
two walls. The macro was to simply tack the date on to the current (text)
contents of a cell and then leave that cell open (i.e. in edit mode) with the
cursor ready for input after the date. The two walls that I hit were (1)
leaving the cell open for input, and (2) retaining the changing font color
formatting of the cell (often phrases or sentences in the cell are different
colors from the rest).

Are there reasonably easy ways around these two problems? I thought of
using a simple form with just a text box instead of allowing the editing
directly in the cell. That could solve the first problem but I still have
the second.

Thanks,
Will

Thanks. I think the F2 thing will take care of putting the cell in edit
mode. As for the colors, I'm not sure yet but will work on using your
suggestion.
Will

"JakeyC" wrote:

This might be work for you. Put the code in a module or a CommandButton
etc. This inserts the text for you, but you can use existing text too.

Sub ColourAndEdit()
Sheets(1).Range("A1").Select
Selection.Value = "Black RED Black " & Date

With ActiveCell.Characters(Start:=7, Length:=3).Font
.Name = "Arial"
.ColorIndex = 3
End With

Application.SendKeys ("{F2}")
End Sub

As much as I distrust and dislike SendKeys it has got me out of many a
situation where I can't do things 'properly'!

The worksheet must be active/visible during execution for this to work.

wrote:
Will,

For problem 2, check out the following link and see if it helps in your
case. I did a search on "change font color part of text" and got a few
good results.

Alan



http://groups.google.com/group/micro...of+text&qt_g=1

roadkill wrote:
I set out to do what I thought would be a simple macro and hit not one, but
two walls. The macro was to simply tack the date on to the current (text)
contents of a cell and then leave that cell open (i.e. in edit mode) with the
cursor ready for input after the date. The two walls that I hit were (1)
leaving the cell open for input, and (2) retaining the changing font color
formatting of the cell (often phrases or sentences in the cell are different
colors from the rest).

Are there reasonably easy ways around these two problems? I thought of
using a simple form with just a text box instead of allowing the editing
directly in the cell. That could solve the first problem but I still have
the second.

Thanks,
Will