Change default color of only new text input into existing cells

**nitrone** · 08-09-2011, 04:38 AM

Hi all,

I want the following:

I've been set to the task of editing an existing spreadsheet.
I have to manually add certain phrases within cells that already have data in them.
The cell font color is originally black and should stay that way, and only the new text input I have to make in these same cells should become red.

Currently, I have to mark the new text and format them to red text manually.

For instance:

Original cell: Jean Claude Van Damme
Formatted cell: Jean Claude Van Damme (finance director)

I was thinking of setting a default color to the whole spread sheet but that does not seem to be possible.

Is there any way to do this more automatically in a simple manner?

Or does anyone have different solutions?

Many thanks! It would save me days of work!

**6StringJazzer** · 08-09-2011, 11:18 AM

This requires a macro. Will a macro solution work for you? Is this a one-time problem?

Even with a macro, it is tricky. A macro can detect the event that is triggered, when a cell is updated, but the event does not capture the value of the cell before the change. Therefore it is not straightforward to figure out what text to turn red.

My first thought on this is that you need a "shadow" hidden worksheet that has the original values. Then when a value changes, it is compared against the original, and any added or changed text is changed to red.

Then we have an even more interesting problem. What kinds of changes will you possibly make? If you are always adding more text at the end, that is fairly straightforward, but if you are adding text in the middle

Jean Claude (butt kicker) Van Damme

or changing text

Jean Paul Van Damme

then the logic gets much more involved.

**nitrone** · 08-10-2011, 02:08 AM

Unfortunately, I'm not very adept at macros yet. So while it could work, it would require quite a detailed recipe for me to follow!

For now it is a one-time problem, but I have done similar things in the past, and who knows whether it will arise again.

As for your last question, it currently only involves adding text to the end of the existing text in the cell.

This spreadsheet is the property of another company (to which I am a consultant), so I would rather not mess around too much with adding "shadow" workbooks and the like.
So in that case, my request would not be possible you think?

Anyhow, thanks for your reply!

**6StringJazzer** · 08-10-2011, 07:25 AM

The approach in that case would be to make a copy of the workbook and put the macros in the copy. Then make your updates to the original. Then when finished, you would run the macro, which would adjust the formatting of your updated original based on comparing it to the baseline in the copy.

It is not a complex problem but is a bit time-consuming and I am not sure when I would have the time to get to it. I will see if I can cook something up this week.