Using Excel 2007. There is a spreadsheet I need to update on a weekly basis.
Every week I need to change all previous data to black font and then enter
my changes in red font. Is there a way to change my default font color for
this single spreadsheet that when I start typing, it will be in red (while
keeping the existing data from previous weeks in the cell in black color
font)? Thanks,

Joe

Joe,

First, change the entire sheet to black font, and then you can use the workbook's sheet change event
to change any newly entered values to a red font.

To do that, copy the code below into the codemodule of the ThisWorkbook object.

If you didn't understand that line, then follow the advice from David McRitchie:
"Unlike standard macros which are installed in standard modules, Workbook Events are installed in
ThisWorkBook in the following manner: F11 (Visual Basic Editor), Get into your project library
(name of your workbook) with Ctrl+R (View, Project Explorer), under the name of your workbook you
see Microsoft Excel Objects, then before Modules you see ThisWorkBook, doubleclick and paste the
code into the code window (F7)."


HTH,
Bernie
MS Excel MVP

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Target.Font.ColorIndex = 3
End Sub


"Joe M" wrote in message
...
Using Excel 2007. There is a spreadsheet I need to update on a weekly basis.
Every week I need to change all previous data to black font and then enter
my changes in red font. Is there a way to change my default font color for
this single spreadsheet that when I start typing, it will be in red (while
keeping the existing data from previous weeks in the cell in black color
font)? Thanks,

Joe

Bernie,

Thank you for that solution. However, for me it is only a partial solution.

It works fine if the cell I'm entering data was empty to begin with, it all
comes up in red font. But if the cell previously had data, it turns the
previously present data in that cell into red color font too. I'd like to
keep the previous data in the cell as black, but the new data by default in
red. Is this possible?

I appreciate the hand-holding to get into visual basic, but there was one
minor correction that is needed, it's alt-F11 to get into the Visual Basic
Editor, not just F11. I kept hitting F11 and making a new chart. Thanks
again,

Joe

"Bernie Deitrick" wrote:

Joe,

First, change the entire sheet to black font, and then you can use the workbook's sheet change event
to change any newly entered values to a red font.

To do that, copy the code below into the codemodule of the ThisWorkbook object.

If you didn't understand that line, then follow the advice from David McRitchie:
"Unlike standard macros which are installed in standard modules, Workbook Events are installed in
ThisWorkBook in the following manner: F11 (Visual Basic Editor), Get into your project library
(name of your workbook) with Ctrl+R (View, Project Explorer), under the name of your workbook you
see Microsoft Excel Objects, then before Modules you see ThisWorkBook, doubleclick and paste the
code into the code window (F7)."


HTH,
Bernie
MS Excel MVP

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Target.Font.ColorIndex = 3
End Sub


"Joe M" wrote in message
...
Using Excel 2007. There is a spreadsheet I need to update on a weekly basis.
Every week I need to change all previous data to black font and then enter
my changes in red font. Is there a way to change my default font color for
this single spreadsheet that when I start typing, it will be in red (while
keeping the existing data from previous weeks in the cell in black color
font)? Thanks,

Joe

Joe,

One question before I can answer: Are you appending strings onto the end of
existing strings? Or do you have numbers that you then append a space and
another number or string?

Sorry about the Alt-F11 - that was from another Excel MVP's website - I will
let him know of the error.

Bernie
MS Excel MVP


"Joe M" wrote in message
...
Bernie,

Thank you for that solution. However, for me it is only a partial
solution.

It works fine if the cell I'm entering data was empty to begin with, it
all
comes up in red font. But if the cell previously had data, it turns the
previously present data in that cell into red color font too. I'd like to
keep the previous data in the cell as black, but the new data by default
in
red. Is this possible?

I appreciate the hand-holding to get into visual basic, but there was one
minor correction that is needed, it's alt-F11 to get into the Visual Basic
Editor, not just F11. I kept hitting F11 and making a new chart. Thanks
again,

Joe

"Bernie Deitrick" wrote:

Joe,

First, change the entire sheet to black font, and then you can use the
workbook's sheet change event
to change any newly entered values to a red font.

To do that, copy the code below into the codemodule of the ThisWorkbook
object.

If you didn't understand that line, then follow the advice from David
McRitchie:
"Unlike standard macros which are installed in standard modules, Workbook
Events are installed in
ThisWorkBook in the following manner: F11 (Visual Basic Editor), Get
into your project library
(name of your workbook) with Ctrl+R (View, Project Explorer), under the
name of your workbook you
see Microsoft Excel Objects, then before Modules you see ThisWorkBook,
doubleclick and paste the
code into the code window (F7)."


HTH,
Bernie
MS Excel MVP

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Target.Font.ColorIndex = 3
End Sub


"Joe M" wrote in message
...
Using Excel 2007. There is a spreadsheet I need to update on a weekly
basis.
Every week I need to change all previous data to black font and then
enter
my changes in red font. Is there a way to change my default font color
for
this single spreadsheet that when I start typing, it will be in red
(while
keeping the existing data from previous weeks in the cell in black
color
font)? Thanks,

Joe

For the most part, they are all text boxes and I add new text to either the
beginning or end of the cell. In all cases, it would definitely be preceded
by a space if I added after the previous text, or would have a space at the
end if I added to the beginning of the existing text. Even on the number
cells, I would definitely have a space before or after, it would not be
appended directly to the previous data.

Joe

"Bernie Deitrick" wrote:

Joe,

One question before I can answer: Are you appending strings onto the end of
existing strings? Or do you have numbers that you then append a space and
another number or string?

Sorry about the Alt-F11 - that was from another Excel MVP's website - I will
let him know of the error.

Bernie
MS Excel MVP


"Joe M" wrote in message
...
Bernie,

Thank you for that solution. However, for me it is only a partial
solution.

It works fine if the cell I'm entering data was empty to begin with, it
all
comes up in red font. But if the cell previously had data, it turns the
previously present data in that cell into red color font too. I'd like to
keep the previous data in the cell as black, but the new data by default
in
red. Is this possible?

I appreciate the hand-holding to get into visual basic, but there was one
minor correction that is needed, it's alt-F11 to get into the Visual Basic
Editor, not just F11. I kept hitting F11 and making a new chart. Thanks
again,

Joe

"Bernie Deitrick" wrote:

Joe,

First, change the entire sheet to black font, and then you can use the
workbook's sheet change event
to change any newly entered values to a red font.

To do that, copy the code below into the codemodule of the ThisWorkbook
object.

If you didn't understand that line, then follow the advice from David
McRitchie:
"Unlike standard macros which are installed in standard modules, Workbook
Events are installed in
ThisWorkBook in the following manner: F11 (Visual Basic Editor), Get
into your project library
(name of your workbook) with Ctrl+R (View, Project Explorer), under the
name of your workbook you
see Microsoft Excel Objects, then before Modules you see ThisWorkBook,
doubleclick and paste the
code into the code window (F7)."


HTH,
Bernie
MS Excel MVP

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Target.Font.ColorIndex = 3
End Sub


"Joe M" wrote in message
...
Using Excel 2007. There is a spreadsheet I need to update on a weekly
basis.
Every week I need to change all previous data to black font and then
enter
my changes in red font. Is there a way to change my default font color
for
this single spreadsheet that when I start typing, it will be in red
(while
keeping the existing data from previous weeks in the cell in black
color
font)? Thanks,

Joe

Joe,

This will fail if the addition is within the string. ie:

This is the original
This is not the original

won't work properly.

HTH,
Bernie
MS Excel MVP

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With Application
.EnableEvents = False
tempval = Target.Value
.Undo
tempval2 = Target.Value
Target.Value = tempval
ActiveCell.Font.ColorIndex = 3

With Target.Characters(Start:=InStr(1, tempval, tempval2), _
Length:=Len(tempval2)).Font
.ColorIndex = xlAutomatic
End With
.EnableEvents = True
End With
End Sub

"Joe M" wrote in message
...
For the most part, they are all text boxes and I add new text to either
the
beginning or end of the cell. In all cases, it would definitely be
preceded
by a space if I added after the previous text, or would have a space at
the
end if I added to the beginning of the existing text. Even on the number
cells, I would definitely have a space before or after, it would not be
appended directly to the previous data.

Joe

"Bernie Deitrick" wrote:

Joe,

One question before I can answer: Are you appending strings onto the end
of
existing strings? Or do you have numbers that you then append a space and
another number or string?

Sorry about the Alt-F11 - that was from another Excel MVP's website - I
will
let him know of the error.

Bernie
MS Excel MVP


"Joe M" wrote in message
...
Bernie,

Thank you for that solution. However, for me it is only a partial
solution.

It works fine if the cell I'm entering data was empty to begin with, it
all
comes up in red font. But if the cell previously had data, it turns
the
previously present data in that cell into red color font too. I'd like
to
keep the previous data in the cell as black, but the new data by
default
in
red. Is this possible?

I appreciate the hand-holding to get into visual basic, but there was
one
minor correction that is needed, it's alt-F11 to get into the Visual
Basic
Editor, not just F11. I kept hitting F11 and making a new chart.
Thanks
again,

Joe

"Bernie Deitrick" wrote:

Joe,

First, change the entire sheet to black font, and then you can use the
workbook's sheet change event
to change any newly entered values to a red font.

To do that, copy the code below into the codemodule of the
ThisWorkbook
object.

If you didn't understand that line, then follow the advice from David
McRitchie:
"Unlike standard macros which are installed in standard modules,
Workbook
Events are installed in
ThisWorkBook in the following manner: F11 (Visual Basic Editor), Get
into your project library
(name of your workbook) with Ctrl+R (View, Project Explorer), under
the
name of your workbook you
see Microsoft Excel Objects, then before Modules you see ThisWorkBook,
doubleclick and paste the
code into the code window (F7)."


HTH,
Bernie
MS Excel MVP

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Target.Font.ColorIndex = 3
End Sub


"Joe M" wrote in message
...
Using Excel 2007. There is a spreadsheet I need to update on a
weekly
basis.
Every week I need to change all previous data to black font and then
enter
my changes in red font. Is there a way to change my default font
color
for
this single spreadsheet that when I start typing, it will be in red
(while
keeping the existing data from previous weeks in the cell in black
color
font)? Thanks,

Joe