Change default font color for a single spreadsheet?

[Joe M]

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 Deitrick]

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]

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]

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]

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]

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]

Bernie,

Thank you for continuing to work with me on this. It's definitely better,
but not quite all the way there. I tried your latest solution and here's my
results:

It worked fine when there was data already in the cell, I could add text
before or after the existing data and it showed up with red font (while
maintaining the previously existing data in black). Great!

2 Problems noted:
1) Entering data in empty cells came up as black. The first solution you
provided did this correctly. Is there any way to combine the two solutions?

2) Using the latest solution provided the "undo" functionality is no longer
present. Is it possible to keep the "undo" functionality with this solution?

Thanks,

Joe

 

[Bernie Deitrick]

Joe,

Try the version below.

The use of the code clears the undo stack, so you cannot do what you want and still have the user be
able to "undo".

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
If tempval2 <> "" Then
With Target.Characters(Start:=InStr(1, tempval, tempval2), _
Length:=Len(tempval2)).Font
.ColorIndex = xlAutomatic
End With
End If
.EnableEvents = True
End With
End Sub

 

[Joe M]

Bernie,

That's okay, I can get by without the undo, it's worth it to get the red
working right.

This worked to a point. The 2 problems I found:

1) If I appended data to existing data in a cell, it worked fine. If I
entered data into an empty cell it worked fine. If I went to a cell with
data, and just started typing (i.e., erasing the existing data by default),
this did not work, the newly entered text stayed black.

2) When I went to insert a new column, I got a 'Run-time error '424': Object
required' message, and your code stops working after that point.

Thank you for continuing to work on this!

Joe

 

[Bernie Deitrick]

Joe,

This should address the insertion of rows and columns, and also allows for
completely over-written cells to be turned red. But it will then limit your
ability to modify changes to multiple cells using Ctrl-Enter.

HTH,
Bernie
MS Excel MVP

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 255 Then Exit Sub
Dim tempVal As Variant
Dim tempVal2 As Variant

With Application
.EnableEvents = False
tempVal = Target.Cells(1).Value
.Undo
tempVal2 = Target.Cells(1).Value
Target.Value = tempVal
Target.Font.ColorIndex = 3
If tempVal2 <> "" Then
If InStr(1, tempVal, tempVal2) > 0 Then
With Target.Characters( _
Start:=InStr(1, tempVal, tempVal2), _
Length:=Len(tempVal2)).Font
.ColorIndex = xlAutomatic
End With
End If
End If
.EnableEvents = True
End With
End Sub

 

[Joe M]

YES! All working great. Thank you very much.

I rarely use Ctrl-Enter, so that won't be a problem.

Giving up the undo-redo is unfortunate, but worth it for what I gain.
Thanks again Bernie!

Joe