Hello everyone,

I am creating a sub that takes the value of a range into another one as
a formula
i.e. Range("TargetCell").FormulaR1C1 "=SourceCell"
So it can be updated automatically when SourceCell changes.

I would like to know if it is possible to copy or get the format of the
SourceCell to apply it also to the TargetCell.

For example:
SourceCell value is "Hello John" with the word "John" in bold
characters.
Currently, TargetCell will display "Hello John" without the bold
characters.

How can I do to also get the word "John" in bold characters while
keeping the "dynamicity" of the cell ?

Thanks you for your help !

If you leave the formula in the cell, then you can't use this character by
character formatting.

If you could use values, you could copy|paste.

cdil wrote:

Hello everyone,

I am creating a sub that takes the value of a range into another one as
a formula
i.e. Range("TargetCell").FormulaR1C1 "=SourceCell"
So it can be updated automatically when SourceCell changes.

I would like to know if it is possible to copy or get the format of the
SourceCell to apply it also to the TargetCell.

For example:
SourceCell value is "Hello John" with the word "John" in bold
characters.
Currently, TargetCell will display "Hello John" without the bold
characters.

How can I do to also get the word "John" in bold characters while
keeping the "dynamicity" of the cell ?

Thanks you for your help !

--

Dave Peterson

if A1 is in bold but B1 isn't then you can do something like:

range("A1").copy
range("B1").pastespecial xlformats

Rgds
J

On 29 Nov, 15:10, "cdil" wrote:
Hello everyone,

I am creating a sub that takes the value of a range into another one as
a formula
i.e. Range("TargetCell").FormulaR1C1 "=SourceCell"
So it can be updated automatically when SourceCell changes.

I would like to know if it is possible to copy or get the format of the
SourceCell to apply it also to the TargetCell.

For example:
SourceCell value is "Hello John" with the word "John" in bold
characters.
Currently, TargetCell will display "Hello John" without the bold
characters.

How can I do to also get the word "John" in bold characters while
keeping the "dynamicity" of the cell ?

Thanks you for your help !

Thanks guy for your help :)
Unfortunatelly with a copy & paste (even by pasting only formats), I
can't preserve the "dynamicity" :(

Other ideas ?


WhytheQ wrote:
if A1 is in bold but B1 isn't then you can do something like:

range("A1").copy
range("B1").pastespecial xlformats

Rgds
J

On 29 Nov, 15:10, "cdil" wrote:
Hello everyone,

I am creating a sub that takes the value of a range into another one as
a formula
i.e. Range("TargetCell").FormulaR1C1 "=SourceCell"
So it can be updated automatically when SourceCell changes.

I would like to know if it is possible to copy or get the format of the
SourceCell to apply it also to the TargetCell.

For example:
SourceCell value is "Hello John" with the word "John" in bold
characters.
Currently, TargetCell will display "Hello John" without the bold
characters.

How can I do to also get the word "John" in bold characters while
keeping the "dynamicity" of the cell ?

Thanks you for your help !

If the original cell is changing because of typing, you could use a
worksheet_change event to do the copy|paste (and formats) for you.

If the original cell is changing because of a calculation/formula, then you
could use a worksheet_calculate event to do the copy|paste (and formats) for
you.

But both of these mean that the "receiving" cell will not contain a formula.

cdil wrote:

Thanks guy for your help :)
Unfortunatelly with a copy & paste (even by pasting only formats), I
can't preserve the "dynamicity" :(

Other ideas ?

WhytheQ wrote:
if A1 is in bold but B1 isn't then you can do something like:

range("A1").copy
range("B1").pastespecial xlformats

Rgds
J

On 29 Nov, 15:10, "cdil" wrote:
Hello everyone,

I am creating a sub that takes the value of a range into another one as
a formula
i.e. Range("TargetCell").FormulaR1C1 "=SourceCell"
So it can be updated automatically when SourceCell changes.

I would like to know if it is possible to copy or get the format of the
SourceCell to apply it also to the TargetCell.

For example:
SourceCell value is "Hello John" with the word "John" in bold
characters.
Currently, TargetCell will display "Hello John" without the bold
characters.

How can I do to also get the word "John" in bold characters while
keeping the "dynamicity" of the cell ?

Thanks you for your help !

--

Dave Peterson

That solution worked out perfectly :)
Thanks a lot !


Jim Thomlinson a écrit :

This is about a close as you will get. It will be dynaic in terms of updating
the formats when the active cell is moved. If you update the format of the
source cell or change the address so the source cell the target cell will be
updated as soon as the active cell is changed. Place this code directly into
the sheet (right click the sheet tab and select view code)...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("SourceCell").Copy Range("TargetCell")
Range("TargetCell").Formula = "=SourceCell"
End Sub
--
HTH...

Jim Thomlinson


"cdil" wrote:

Hello everyone,

I am creating a sub that takes the value of a range into another one as
a formula
i.e. Range("TargetCell").FormulaR1C1 "=SourceCell"
So it can be updated automatically when SourceCell changes.

I would like to know if it is possible to copy or get the format of the
SourceCell to apply it also to the TargetCell.

For example:
SourceCell value is "Hello John" with the word "John" in bold
characters.
Currently, TargetCell will display "Hello John" without the bold
characters.

How can I do to also get the word "John" in bold characters while
keeping the "dynamicity" of the cell ?

Thanks you for your help !