What I have to use ...

[Guest]

In Excel2002 or newer versions
Is it possible to have destination cells to respect/follow the format of
referenced cells?

If yes HOW? If no WHY?

Here is an example
In a1 I have -1234,00 with red fontcolor
In a2 I have 31/05/2007
In b1 I have "a1" without quotes
In b2 I have "a2" without quotes
In c1 I have =INDIRECT(b1) and it gives -1234 in black fontcolor
In c2 I have =INDIRECT(b2) and it gives 39233
In d1 I have =a1 and result is the same as in c1
In d2 I have =a2 and result is 39233,00

The question is:
What I have to use in order without copy paste formats cell by cell (since
target cells can be anywhere in my worksheets) to obtain in destination cells
(c1,c2 and/or d1,d2 in the example) the same format as their references (a1
and a2 in the example)

Thank you in advance for your time.
PS I am using Excel2002 and/or Excel2003 and I am in Europe (separator “;â€
decimal-separator “,†and date-formats dd/mm/yyyy)

 

[Dave Peterson]

Formulas don't bring this kind of formatting back.

You can do copy|paste (all manual).

Or maybe you could use some sort of worksheet event (_calculate or _change
probably) that would copy|paste formats.

You'd have to define each sending cell and each receiving cell and how that
sending cell changes (is it a formula or does the user enter the value).

 

[Guest]

Thank you for your reply Dave.

The problem raised when in an existing book cells changed purpose and role
where their format was cleared.
So I look forward what to do in the future to avoid such bad surprises.

It is not very clever for Excel to not be able to keep/transfer such info
from origin to destination.

If you could develop more your idea of worksheet events I am interested.
I am afraid it will not be very simple, besides my VBA knowledge is not too
low.

I will wait also maybe someone else to come up with more ideas or references.

Thank you anyway for your reply.

 

[Dave Peterson]

Some references:

David McRitchie's intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

 

[Guest]

Great
I had a quick look and I think I will work further on
"Color Dependent cells with same Color Font as Active Cell" from David
McRitchie
Or as a first approach I will try to locate all dependents of concerned
cells and copy/paste format as you said.

It is a pitty Excel does not do this automatically :-(
So I have some work to do

Thanks again.

 

[Dave Peterson]

I'm not sure how many cells are involved, but it may be easier to specify the
addresses of each sending cell/range and the address of each receiving
cell/range.

Great
I had a quick look and I think I will work further on
"Color Dependent cells with same Color Font as Active Cell" from David
McRitchie
Or as a first approach I will try to locate all dependents of concerned
cells and copy/paste format as you said.

It is a pitty Excel does not do this automatically :-(
So I have some work to do

Thanks again.